Select Distinct error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ToniS - 23 Jul 2007 22:38 GMT

I get the following error w/ the below select statement:
ADO error: ORDER BY items must appear in the select list if SELECT DISTINCT
is specified

I am trying to select the first booth number for an exhibitor to print on a
report and to print the Information in boothName order, which is a varchar15
column.

Ex data (Booth, Exhibitor)

1    Exh1
13    Exh1
33    Exh2
17    Exh2
Mesquite    Exh3
Adams    Exh3

I would like the results to be as follows:


Adams    Exh3
1    Exh1
17    Exh2

SELECT DISTINCT TOP 100 PERCENT dbo.Exhibitors.ExhibitorName,
MIN(dbo.ExhibitorShowBooths.BoothName) AS FirstBooth,
dbo.ExhibitorsShows.ExhibitorID
FROM dbo.ExhibitorsShows INNER JOIN
dbo.Exhibitors ON dbo.ExhibitorsShows.ExhibitorID =
dbo.Exhibitors.ExhibitorID INNER JOIN
dbo.ExhibitorShowBooths ON
dbo.ExhibitorsShows.ExhibitorShowID = dbo.ExhibitorShowBooths.ExhibitorShowID
GROUP BY dbo.ExhibitorsShows.ExhibitorID, dbo.Exhibitors.ExhibitorName
ORDER BY CASE WHEN BoothName LIKE '[0-9]%' THEN LEN(BoothName) ELSE 0 END,
dbo.ExhibitorShowBooths.BoothName

Any suggestions are greatly appreciated
Thanks
ToniS

Reply to this Message

Sylvain Lafontaine - 24 Jul 2007 02:33 GMT

Things like DISCTINCT and GROUP BY are opposite; remove the « DISTINCT TOP
100 PERCENT ». Second, « BoothName » is not part of the Select list; so you
should either add it to the Select List or make your Order By on the
FirstBooth column.

Don't forget that after the Group By, any direct relationship between a
column and it's underlying table is lost (or something like that).

For more complexe Grouping, Ordering and Presentation of data, you might
have to use sub-queries.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

>I get the following error w/ the below select statement:
> ADO error: ORDER BY items must appear in the select list if SELECT
[quoted text clipped - 38 lines]
> Thanks
> ToniS

Reply to this Message

ToniS - 24 Jul 2007 15:12 GMT

Thanks for your input, I have tried a whole bunch of different things,
including removing the Disctinct Top 100 Percent, I will try it again w/ the
below select statement, (Not sure how I had the select statement when I first
tried that) If that does not work, the suggestion of using sub queries is a
good idea, I will do that.

Thanks again
Tonis

> Things like DISCTINCT and GROUP BY are opposite; remove the « DISTINCT TOP
> 100 PERCENT ». Second, « BoothName » is not part of the Select list; so you
[quoted text clipped - 49 lines]
> > Thanks
> > ToniS

Reply to this Message