Order BY Problem? - SQL Server Forums

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

Order BY Problem?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-02-17 : 04:30:58

Hi,

I would like to order by desc the below query. Any help would be great.

Best Regards.

Here is the query:


select distinct Users.name, CONVERT(CHAR(10),Eform_Masraf.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_Masraf.CreationDate,113),12,9)  as CreationDate,SUM(Eform_Masraf_Kalem.MasrafTutar) AS MasrafTutar,CurrencyTypes.Currency,Eform_Masraf.EformID,Eform_Masraf.EformType,Eform_Masraf.IsApproved,Eform_Masraf.CreatedBy from Eform_Masraf,Eform_Masraf_Kalem, Users,CurrencyTypes where Eform_Masraf_Kalem.EFormID=Eform_Masraf.EFormID and Eform_Masraf.CreatedBy = Users.SAMAccountName and CurrencyTypes.CurrencyID = Eform_Masraf_Kalem.ParaBirimi group by Users.name,Eform_Masraf.CreationDate,CurrencyTypes.Currency,Eform_Masraf.EformID,Eform_Masraf.EformType,Eform_Masraf.IsApproved,Eform_Masraf.CreatedBy

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-17 : 04:42:44

Order by what column?

--
Gail Shaw
SQL Server MVP

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2012-02-17 : 04:43:39

just add:
Order By
[Column_name],[Column_name]...
desc
at the end of the query

kalyan Ashis Dey

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-02-17 : 04:43:57

so sorry , order by CreationDate

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-17 : 04:58:07

Turned into a readable form...


SELECT DISTINCT
        Users.name ,
        CONVERT(CHAR(10), Eform_Masraf.CreationDate, 104)
        + SUBSTRING(CONVERT(VARCHAR, Eform_Masraf.CreationDate, 113), 12, 9) AS CreationDate ,
        SUM(Eform_Masraf_Kalem.MasrafTutar) AS MasrafTutar ,
        CurrencyTypes.Currency ,
        Eform_Masraf.EformID ,
        Eform_Masraf.EformType ,
        Eform_Masraf.IsApproved ,
        Eform_Masraf.CreatedBy
FROM    Eform_Masraf ,
        Eform_Masraf_Kalem ,
        Users ,
        CurrencyTypes
WHERE   Eform_Masraf_Kalem.EFormID = Eform_Masraf.EFormID
        AND Eform_Masraf.CreatedBy = Users.SAMAccountName
        AND CurrencyTypes.CurrencyID = Eform_Masraf_Kalem.ParaBirimi
GROUP BY Users.name ,
        Eform_Masraf.CreationDate ,
        CurrencyTypes.Currency ,
        Eform_Masraf.EformID ,
        Eform_Masraf.EformType ,
        Eform_Masraf.IsApproved ,
        Eform_Masraf.CreatedBy
ORDER BY CreationDate DESC

--
Gail Shaw
SQL Server MVP

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-02-17 : 05:27:45

Hi Gail,

It is NOT ordering like DESC

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-17 : 06:07:16

Moral of the story, don't alias columns the same as the actual column name...
It is ordering by CreationDate DESC, it's ordering by the string representation of the date that you specified in the select clause.
I guess from your reply is that you don't want it ordered by CreationDate
(which is defined as CONVERT(CHAR(10), Eform_Masraf.CreationDate, 104) + SUBSTRING(CONVERT(VARCHAR, Eform_Masraf.CreationDate, 113), 12, 9)),
but by the underlying column.
It would have helped if you'd stated that up front, I can't read your mind.

Does that Distinct have to be there, or is it just there out of habit?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources