Select DISTINCT with dynamic CASE ORDER BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select DISTINCT with dynamic CASE ORDER BY

Author

Topic

djchrome
Starting Member

19 Posts

Posted - 2002-08-12 : 13:22:38

I am having problems using DISTINCT in my select statement with a dynamic ORDER BY. Can someone let please take a look and let me know if I'm missing something. I get back the error "Error 145: ORDER BY itmes must appear in the select list if SELECT DISCINT is specified. As far as I can tell I followed that rule. Thanks!

The FULL code is as follows:

SELECT DISTINCT TOP 100 PERCENT dbo.Companies.CompanyID, dbo.Companies.CompanyCode, dbo.Companies.CompanyName, (SELECT TOP 1 dbo.CompanyLocations.CompanyLocationPhone1 FROM dbo.CompanyLocations
WHERE dbo.CompanyLocations.CompanyID = dbo.Companies.CompanyID) AS CompanyLocationPhone1, dbo.Companies.CompanyStatus, ISNULL(dbo.Company_OpenJobs_Count.JobCount, 0) AS OpenCount, ISNULL(dbo.Company_ReadyToBillJobs_Count.JobCount, 0) AS ReadyCount,
dbo.Companies.CompanyType

FROM dbo.Companies INNER JOIN
dbo.CompanyContacts ON dbo.Companies.CompanyAccountManager = dbo.CompanyContacts.CompanyContactID INNER JOIN
dbo.CompanyLocations ON dbo.Companies.CompanyID = dbo.CompanyLocations.CompanyID LEFT OUTER JOIN
dbo.Company_ReadyToBillJobs_Count ON dbo.Companies.CompanyID = dbo.Company_ReadyToBillJobs_Count.CompanyID LEFT OUTER JOIN
dbo.Company_OpenJobs_Count ON dbo.Companies.CompanyID = dbo.Company_OpenJobs_Count.CompanyID

WHERE (dbo.Companies.CompanyAccountManager = @CompanyContactID) AND (dbo.Companies.CompanyType = COALESCE (@CompanyType, dbo.Companies.CompanyType)) AND (dbo.Companies.CompanyStatus = COALESCE (@CompanyStatus, dbo.Companies.CompanyStatus))

ORDER BY
(CASE @OrderBY
WHEN 1 THEN CAST(dbo.Companies.CompanyCode AS VARCHAR(50))
WHEN 3 THEN CAST(dbo.Companies.CompanyName AS VARCHAR(50))
WHEN 5 THEN CAST(dbo.Companies.CompanyStatus AS VARCHAR(50))
WHEN 7 THEN CAST(dbo.Companies.CompanyType AS VARCHAR(50))
END) DESC,

(CASE @OrderBY
WHEN 2 THEN CAST(dbo.Companies.CompanyCode AS VARCHAR(50))
WHEN 4 THEN CAST(dbo.Companies.CompanyName AS VARCHAR(50))
WHEN 6 THEN CAST(dbo.Companies.CompanyStatus AS VARCHAR(50))
WHEN 8 THEN CAST(dbo.Companies.CompanyType AS VARCHAR(50))
END) ASC

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-12 : 13:57:53

You need to CAST the select list as well if you are CASTING in the ORDER BY.

SELECT DISTINCT TOP 100 PERCENT dbo.Companies.CompanyID, CAST(dbo.Companies.CompanyCode AS VARCHAR(50)) as CompanyCode, CAST(dbo.Companies.CompanyName AS VARCHAR(50)) as CompanyName, (SELECT TOP 1 dbo.CompanyLocations.CompanyLocationPhone1 FROM dbo.CompanyLocations
WHERE dbo.CompanyLocations.CompanyID = dbo.Companies.CompanyID) AS CompanyLocationPhone1, CAST(dbo.Companies.CompanyStatus AS VARCHAR(50)) as CompanyStatus, ISNULL(dbo.Company_OpenJobs_Count.JobCount, 0) AS OpenCount, ISNULL(dbo.Company_ReadyToBillJobs_Count.JobCount, 0) AS ReadyCount,
CAST(dbo.Companies.CompanyType AS VARCHAR(50)) as CompanyType

FROM dbo.Companies INNER JOIN
dbo.CompanyContacts ON dbo.Companies.CompanyAccountManager = dbo.CompanyContacts.CompanyContactID INNER JOIN
dbo.CompanyLocations ON dbo.Companies.CompanyID = dbo.CompanyLocations.CompanyID LEFT OUTER JOIN
dbo.Company_ReadyToBillJobs_Count ON dbo.Companies.CompanyID = dbo.Company_ReadyToBillJobs_Count.CompanyID LEFT OUTER JOIN
dbo.Company_OpenJobs_Count ON dbo.Companies.CompanyID = dbo.Company_OpenJobs_Count.CompanyID

WHERE (dbo.Companies.CompanyAccountManager = @CompanyContactID) AND (dbo.Companies.CompanyType = COALESCE (@CompanyType, dbo.Companies.CompanyType)) AND (dbo.Companies.CompanyStatus = COALESCE (@CompanyStatus, dbo.Companies.CompanyStatus))

ORDER BY
(CASE @OrderBY
WHEN 1 THEN CAST(dbo.Companies.CompanyCode AS VARCHAR(50))
WHEN 3 THEN CAST(dbo.Companies.CompanyName AS VARCHAR(50))
WHEN 5 THEN CAST(dbo.Companies.CompanyStatus AS VARCHAR(50))
WHEN 7 THEN CAST(dbo.Companies.CompanyType AS VARCHAR(50))
END) DESC,

(CASE @OrderBY
WHEN 2 THEN CAST(dbo.Companies.CompanyCode AS VARCHAR(50))
WHEN 4 THEN CAST(dbo.Companies.CompanyName AS VARCHAR(50))
WHEN 6 THEN CAST(dbo.Companies.CompanyStatus AS VARCHAR(50))
WHEN 8 THEN CAST(dbo.Companies.CompanyType AS VARCHAR(50))
END) ASC

HTH
Jasper Smith

r937
Posting Yak Master

112 Posts

Posted - 2002-08-12 : 14:11:51

i'm curious as to why you're getting multiple rows per company that you would need a DISTINCT in the first place (lousy grammar, i hope you understand what i mean)

maybe it's because you're joining Companies to CompanyLocations in the FROM clause? it looks like you intended the SELECT subselect for TOP 1 to avoid that

if you use GROUP BY instead, just replace the subselect with MAX(CompanyLocationPhone1)

you'd probably also want to change ISNULL(xxx, 0) to SUM(coalesce(xxx, 0)) and that way be sure of collapsing multiple rows to one for each company

rudy
http://rudy.ca/

djchrome
Starting Member

19 Posts

Posted - 2002-08-12 : 15:04:13

Aaaahaaa! r937 you were right. I didn't have any need to join the CompanyLocations table, so I yanked it out and then had no need for the DISTINCT clause.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources