Select Distinct and 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

General SQL Server Forums

New to SQL Server Programming

Select Distinct and order by...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-12-16 : 15:27:48

When I added the order by to this query it gives me this error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. What am I doing wrong?


ALTER procedure [dbo].[GetT2Docs]

@doc char(3),
@type varchar(1)



as

SELECT distinct(t1.clm) as clm,  n.area, n.dist, t2.doc, t1.[type], convert(char,t1.app_rcpdt, 101) as app_rcpdt,  
	case when t1.location = 'DDS'
	then lorec4
	when t1.location = 'FO'
	then t2.doc
	end location 

from T2pending t1
left join T2Dibpend t2 ON t2.clm = t1.clm
left  join natdocfile n on n.doc=t2.doc
WHERE  type=@type and t2.doc=@doc
order by convert(datetime,t1.app_rcpdt, 103) asc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 16:21:11

SELECT distinct(t1.clm) as clm, n.area, n.dist, t2.doc, t1.[type], convert(varchar(20),t1.app_rcpdt, 101) as app_rcpdt,
case when t1.location = 'DDS'
then lorec4
when t1.location = 'FO'
then t2.doc
end location

from T2pending t1
left join T2Dibpend t2 ON t2.clm = t1.clm
left join natdocfile n on n.doc=t2.doc
WHERE type=@type and t2.doc=@doc
order by convert(varchar(20),t1.app_rcpdt, 101) asc

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-12-16 : 16:50:25

Thanks! I tried that but it's putting it in order by month:
02/02/2010
04/02/2011
04/02/2011
08/10/2010
09/02/2011

How can I get it to come out as such:

02/02/2010
08/10/2010
04/02/2011
04/02/2011
09/02/2011

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 17:02:37

It's best to do the formatting of the returned data in the application and not in T-SQL. So instead, do this:

SELECT distinct(t1.clm) as clm, n.area, n.dist, t2.doc, t1.[type], app_rcpdt,
case when t1.location = 'DDS'
then lorec4
when t1.location = 'FO'
then t2.doc
end location

from T2pending t1
left join T2Dibpend t2 ON t2.clm = t1.clm
left join natdocfile n on n.doc=t2.doc
WHERE type=@type and t2.doc=@doc
order by app_rcpdt

And then strip off the time portion in your application. All programming languages have very easy ways to format date/time data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-12-19 : 14:52:48

Thanks Tkizer! So the DB works harder/slower when I'm converting the date in the stored procedure? Is this why you said to do it in the application?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-19 : 15:03:35

It's unnecessary processing time for SQL to do this. Formatting data is the responsibility of the presentation layer, which is your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-12-19 : 15:40:36

Okay thanks I will do that for now on.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-19 : 15:48:26

only if it's a boat;load of data which would be wrong

just order by the date..don't convert

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources