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