select order by desc not working correctly

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

Transact-SQL (2005)

select order by desc not working correctly

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-08-18 : 16:34:16

I have the following sp: order by revisiondate desc is not working properly.

ALTER PROCEDURE [dbo].[USP_GetDMGridRevisions]
(
@DMid int,
@glanguage nvarchar(20)
)

AS

DECLARE @ErrorCode int

SET NOCOUNT ON
SELECT @ErrorCode = @@Error
IF @ErrorCode = 0
BEGIN
SELECT RV.RevID,

dbo.UDF_DMRevDocExists(RV.RevID) RevDocID,

rtrim(RV.RevNo) as RevNo,

Case --This is revision status by language
When @glanguage='English' Then rtrim(isnull(PV.EnglishText,''))
When @glanguage='Spanish' Then rtrim(isnull(PV.SpanishText,''))
When @glanguage='Catalan' Then rtrim(isnull(PV.CatalanText,''))
End RevStatusText,

RV.RevStatus,

CONVERT(varchar(10),RV.RevisionDate,101) as RevisionDate,
CONVERT(varchar(10),RV.IssuedDate,101) as IssuedDate,
CONVERT(varchar(10),RV.LoggedDate,101) as LoggedDate,
PV.PValue from TAB_ccsNetDMRevision RV, TAB_ccsNetPicklistValue PV
Where RV.dmid=@DMid and RV.RevStatus = PV.Pickid and PV.FieldLabelKey='ddlRevstatus' and RV.Deleted=0

ORDER BY RV.RevisionDate DESC

SELECT @ErrorCode = @@Error
END
SET NOCOUNT OFF
RETURN @ErrorCode

when i execute the above sp, i am getting 2 rows
showing revision dates as follows: year 2010 row should appear top but it is showing as second row. am i doing something wrong.

Revision date:
12/10/2009
08/12/2010

Thank you very much for the helpful info.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-18 : 16:50:27

What data type is RevisionDate? It sounds like it is varchar, which is not correct for dates.

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

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-18 : 17:15:18

if it is do

ORDER BY CONVERT(varchar(10),RV.RevisionDate,101) DESC

but really what tkizer is saying is on the money

If you don't have the passion to help people, you have no passion

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-08-18 : 17:17:16

Tara, it is datetime field.

but in the sp i did convert it to varchar.

But i guess that has nothing to do with orderby still in table design it is datetime field.

quote:
Originally posted by tkizer

What data type is RevisionDate? It sounds like it is varchar, which is not correct for dates.

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

Subscribe to my blog

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-08-18 : 17:24:49

Tara, the problem is happening due to this:
CONVERT(varchar(10),RV.RevisionDate,101) as RevisionDate,

inside the sp i am converting teh revisiondate to varchar.

and using order by revisiondate desc, it is using the converted varchar date data.

quote:
Originally posted by tkizer

What data type is RevisionDate? It sounds like it is varchar, which is not correct for dates.

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-18 : 17:27:57

Remove your conversions from the stored procedure. Do the date/time formatting in the application instead. Formatting of data should not be done inside SQL.

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

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 17:38:39

long shot:

Change to:

CONVERT(varchar(10),RV.RevisionDate,101) as RevisionDatexxxxx,

to avoid ambiguity on RevisionDate name (notwithstanding that you do appear to have unambiguously named it with the table alias as a prefix anyway ... I did say it was a longshot!)

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 17:40:31

"and using order by revisiondate desc, it is using the converted varchar date data"

I disagree, although that was the premise of my comment above.

Your ORDER by is

ORDER BY RV.RevisionDate DESC

which should use the value from the RV table alias and not the value from the named column in the SELECT clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-18 : 17:40:31

I don't think that's a long shot, that's the problem with the query if the conversions are left in. My vote is to remove them as the best practice is not to do formatting in SQL.

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

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 17:42:24

Yeah, I definitely agree that there is room for misunderstanding! so just from a code maintenance perspctive I would avoid having the same name for a column in the table, and a column in the Select that was "manipulated".

Plus, as you will know

, I also agree with "format it in the Application / Front end"

But notwithstanding any of that I reckon this puppy SHOULD work as written!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-19 : 05:37:59

quote:
Originally posted by Kristen

"and using order by revisiondate desc, it is using the converted varchar date data"

I disagree, although that was the premise of my comment above.

Your ORDER by is

ORDER BY RV.RevisionDate DESC

which should use the value from the RV table alias and not the value from the named column in the SELECT clause.

This is a behavioral change in versions starting from 2005. For versions prior to 2005, the ordering done by the actual alias name

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-08-19 : 11:08:25

I assumed SQL 2005 as that is this conference

but, yeah, if we are back in SQL 2000 then all bets are off as to which data object will be used when an ambiguous ORDER BY name is used

That was pretty much our only, but (for us) relatively major, fix when upgrading from SQL 2000. We had lots of instances of ORDER BY columns used in the SELECT in a way that upset SQL2005+

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-19 : 11:17:20

quote:
Originally posted by Kristen

I assumed SQL 2005 as that is this conference but, yeah, if we are back in SQL 2000 then all bets are off as to which data object will be used when an ambiguous ORDER BY name is used

That was pretty much our only, but (for us) relatively major, fix when upgrading from SQL 2000. We had lots of instances of ORDER BY columns used in the SELECT in a way that upset SQL2005+

My guess is OP uses version 2000 and posted a question at 2005 forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources