Select Distinct error

[Guest]

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

 

[Sylvain Lafontaine]

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.

 

[Guest]

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
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.

--
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
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