SELECT statement with DISTINCT and ORDER BY clause

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

Transact-SQL (2000)

SELECT statement with DISTINCT and ORDER BY clause

Author

Topic

Rocko
Starting Member

26 Posts

Posted - 2006-05-17 : 11:42:11

Hi Friends,

I’m using SELECT statement with DISTINCT and ORDER BY clause in Procedure. Below is a sample code I created with Northwind Database:

declare @sortExpression tinyint
Select distinct e.EmployeeID, e.LastName
from Employees e
inner join Orders o on o.EmployeeID = e.EmployeeID
ORDER BY (CASE WHEN @sortExpression = 1 THEN e.EmployeeID
ELSE e.LastName
END) DESC

I’m getting the following error message:
Server: Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I have my columns in my Select statement. I know that removing the ‘distinct’ will fix the issue but I really need to use it.
I was wondering if anybody is familiar with that type of error.

Thanks in advance!
Rocko

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-17 : 12:13:54

try
declare @sortExpression tinyint
select * from
(Select distinct e.EmployeeID, e.LastName
from Employees e
inner join Orders o on o.EmployeeID = e.EmployeeID
) a
ORDER BY (CASE WHEN @sortExpression = 1 THEN e.EmployeeID
ELSE e.LastName
END) DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sshelper
Posting Yak Master

216 Posts

Posted - 2006-05-17 : 12:14:07

Here's a couple of links that may be able to help you on working around this error:

http://www.sql-server-helper.com/faq/error-messages-p05.aspx
http://www.sql-server-helper.com/error-messages/msg-145.aspx

SQL Server Helper
http://www.sql-server-helper.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-17 : 12:20:43

How about the following

Use Northwind 

declare @sortExpression tinyint
Select  e.EmployeeID, e.LastName
from Employees e
inner join Orders o on o.EmployeeID = e.EmployeeID
Group by e.EmployeeID, e.LastName
ORDER BY  CASE WHEN @sortExpression = 1 THEN convert(varchar(10),e.EmployeeID)
ELSE e.LastName
END  DESC

-- Here convert(varchar(10),e.EmployeeID) to prevent some other error

Srinika

Rocko
Starting Member

26 Posts

Posted - 2006-05-17 : 13:00:21

Hi,

I tried all of the solutions but with no success.

Nigel,
The following statement:

declare @sortExpression tinyint
select * from
(Select distinct e.EmployeeID, e.LastName
from Employees e
inner join Orders o on o.EmployeeID = e.EmployeeID
) a
ORDER BY (CASE WHEN @sortExpression = 1 THEN e.EmployeeID
ELSE e.LastName
END) DESC

will raise the following error because the prefixes mismatch

Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'e' does not match with a table name or alias name used in the query.

Srinika,

Your statement is working fine but because you don’t use distinct in the select statement. But if you try with distinct will raise the same error as I got from my statement.

Do you have any other ideas folks?
Thanks
Rocko

Maux Maux
Starting Member

10 Posts

Posted - 2006-05-17 : 14:11:53

nr almost had it, just change the 'e' to 'a'
and add in the convert


declare @sortExpression tinyint

select * from
(Select distinct e.EmployeeID, e.LastName
from Employees e
inner join Orders o on o.EmployeeID = e.EmployeeID
) a
ORDER BY CASE WHEN @sortExpression = 1 
THEN CONVERT(VARCHAR(10),a.EmployeeID)
ELSE a.LastName
END  DESC

EDIT: fix my boo boo

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-17 : 14:56:49

quote:
Originally posted by Rocko

Srinika,

Your statement is working fine but because you don’t use distinct in the select statement. But if you try with distinct will raise the same error as I got from my statement.

U'll get the same results from both ways.

What is ur requirement :
'Use distinct somehow" or get the desired results ?

Srinika

Rocko
Starting Member

26 Posts

Posted - 2006-05-17 : 18:01:52

Hi,

Thanks Maux Maux and Srinika,

Both solutions work excellent for me. I used to work mostly with distinct rather than Group By. As I understand the Group By will always eliminate the duplicate values in the select statement. Is that correct?
And also from the performance point which solution is better. The one using select * and then select distinct or using Group By.

Thanks again!

Rocko
Starting Member

26 Posts

Posted - 2006-05-18 : 22:34:20

Hi!

I’m stack again and wondering how will look like any of the above statements if I want to use variable @direction for ASC or DESC?

Thanks!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-18 : 23:10:44

There's a million ways to do this. Here's the most confusing.


CREATE PROCEDURE djlOrderMadness 

	@sortExpression TINYINT,
	@sortDirection VARCHAR(4)

AS

SELECT
	dt.EmployeeID,
	dt.LastName
FROM (
	SELECT DISTINCT
		e.EmployeeID, 
		e.LastName
	FROM 
		Employees e
		INNER JOIN Orders o ON o.EmployeeID = e.EmployeeID) dt
ORDER BY 
	CASE 
		WHEN @sortExpression = 1 AND @sortDirection = 'ASC' 
		THEN CONVERT(VARCHAR(10),dt.EmployeeID) 
	END ASC,
	CASE 
		WHEN @sortExpression = 1 AND @sortDirection = 'DESC' 
		THEN CONVERT(VARCHAR(10),dt.EmployeeID) 
	END DESC,
	CASE
		WHEN @sortExpression <> 1 AND @sortDirection = 'ASC' 
		THEN dt.LastName
	END ASC,
	CASE
		WHEN @sortExpression <> 1 AND @sortDirection = 'DESC' 
		THEN dt.LastName
	END DESC
GO
EXEC djlOrderMadness 1,'ASC'
EXEC djlOrderMadness 1,'DESC'
EXEC djlOrderMadness 2,'ASC'
EXEC djlOrderMadness 2,'DESC'
GO
DROP PROCEDURE djlOrderMadness
GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-19 : 03:19:39

I wonder why everyone failed to see that you dont need DISTINCT at all, if all you want is
Employees that has Orders, then this would be both simpler and performe better:

SELECT e.EmployeeID, e.LastName
FROM Employees e
WHERE EXISTS (SELECT * FROM Orders o WHERE o.EmployeeID = e.EmployeeID)
ORDER BY (CASE WHEN @sortExpression = 1 THEN e.EmployeeID
ELSE e.LastName
END) DESC

and if you want 'derrickleggett's golden solution:

SELECT
	e.EmployeeID,
	e.LastName
FROM Employees e
WHERE EXISTS (SELECT * FROM Orders o ON o.EmployeeID = e.EmployeeID)
ORDER BY 
	CASE 
		WHEN @sortExpression = 1 AND @sortDirection = 'ASC' 
		THEN e.EmployeeID 
	END ASC,
	CASE 
		WHEN @sortExpression = 1 AND @sortDirection = 'DESC' 
		THEN e.EmployeeID 
	END DESC,
	CASE
		WHEN @sortExpression <> 1 AND @sortDirection = 'ASC' 
		THEN e.LastName
	END ASC,
	CASE
		WHEN @sortExpression <> 1 AND @sortDirection = 'DESC' 
		THEN e.LastName
	END DESC

I removed the unnessesery CONVERT(), it only applies when you mix different types like in 'Srinika' pointed out.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Rocko
Starting Member

26 Posts

Posted - 2006-05-19 : 09:35:10

Hi,

Thanks a lot for the examples. I was using DISTINCT because my real solution is with it.
Rocko

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-19 : 09:43:13

And your real solution uses it because of what?

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Rocko
Starting Member

26 Posts

Posted - 2006-05-19 : 09:49:21

Because otherwise is retrieving duplicate records. Other option is to use Group By which should get the same result?

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-19 : 10:01:11

We may be talking passed each other, my point was just that there was no need for you to use neither DISTINCT nor GROUP BY to get rid of duplicates.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Rocko
Starting Member

26 Posts

Posted - 2006-05-19 : 11:52:45

So what should I use if I want to be sure that the result won’t have any duplicate records? Is there anything else I can get rid of duplicates if I need to?
I was using the above statement for simplicity reasons. But my real statement is returning duplicate records if I don't use Distinct or Group By. Let me know if I need to explain more.

Maux Maux
Starting Member

10 Posts

Posted - 2006-05-22 : 12:07:47

"
I wonder why everyone failed to see that you dont need DISTINCT at all, if all you want is
Employees that has Orders, then this would be both simpler and performe better:
"

PSamsig, you missed the 4th post in this thread.

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-05-22 : 14:42:14

Rocko, PSamsig's point is that by querying against just the employees table, there will never be duplicates.. Your duplicates are created by the join to the orders table (when an employee has multiple orders). PSamsig's query does not use the join, so no duplicates and no need for DISTINCT or GROUP BY..

I think this thread shows why trying to use a simplified example based on a real life need can sometimes cause more confusion....

Rocko
Starting Member

26 Posts

Posted - 2006-05-22 : 14:47:07

I agree. Thanks for that!

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:05:10

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources