complex 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

SQL Server 2000 Forums

Transact-SQL (2000)

complex order by problem

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-04 : 11:17:34

I'm having difficulty with a query I'm making...well, attempting.

I'm working with 5 columns here: Name, SeNo, OrderNumber, Type and expirationDate

the query looks like this:


Select Name, SeNumber, OrderNumber, 
Type = (Select Case 
When ISNULL(ExpirationDate, '1/10/2020') <= getdate() Then '[Retired]' + '(' + Type + ') '
When ISNULL(ExpirationDate, '1/10/2020') > getdate() Then '(' + Type = ') ' 
END), 
ExpirationDate
From MapTable
Order by SeNo, OrderNUmber

OrderNumber is a designation of numeric order, used in conjunction with Type. Basically, it ranges from 1 to N for each Type.

Now, here's the problem I'm having:

The query result may look like this:


Name    SeNo      OrderNumber     Type                 ExpirationDate
---------------------------------------------------------------------
John    100       1               [Retired](Physical)  12/31/2021
Jane    100       2               Physical             11/11/2000
Joe     100       3               [Retired](Email)     11/12/2022
Mary    100       4               Email                10/10/1999
Alan    101       1               [Retired](Physical)  12/31/2021
Gary    101       2               Physical             11/11/2000
Kate    101       3               [Retired](Email)     11/12/2022
Marie   101       4               Email                10/10/1999

I want it to look like this:


Name    SeNo      OrderNumber     Type                 ExpirationDate
--------------------------------------------------------------------
Jane    100       2               Physical             11/11/2000
Mary    100       4               Email                10/10/1999
John    100       1               [Retired](Physical)  12/31/2021
Joe     100       3               [Retired](Email)     11/12/2022
Gary    101       2               Physical             11/11/2000
Marie   101       4               Email                10/10/1999
Alan    101       1               [Retired](Physical)  12/31/2021
Kate    101       3               [Retired](Email)     11/12/2022

See that? It orders it on SeNo, then based upon if the Type has "[Retired]" prepended to Type, and finally the OrderNumber. But the caveat here is the orderno is predicated on the Type and I am not sure how to realize this query result. That is the problem at hand. If anyone can help me, I'd really appreciate it. Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 11:22:53

[code]Order by
SeNo,
Case when ISNULL(ExpirationDate, '1/10/2020') <= getdate() then 1 else 0 end, OrderNumber[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-04 : 11:38:16

Wow! Thank you very much. You figured that out pretty quickly.

Question? How does this work exactly? I'm somewhat familar with subqueries in the order by clause. If I understand it correctly, all the non- "[Retired]" values are represented by 1; otherwise, it's ordered by 0.

I tried using select count(*) in the order by clause, but it was not giving me the correct results, unlike your solution. I'll remember it in the future, for sure.

Thanks again.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 11:44:48

What I posted is not subquery, it's simple CASE expression. It just dictate order based on condition. Since you wanted "[Retired]" records down in the order, I gave them higher rank i.e.1 than normal records, hence the result.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-04 : 13:52:23

quote:
Originally posted by SQLIsTheDevil

Wow! Thank you very much. You figured that out pretty quickly.

Hence the title "Flowing Fount of Yak Knowledge"

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources