Help on Order By !

Hi All,
I have a requirement which need to order by in certain way. For example, i have a list of columns which selects from joining multiple tables but one column needs to be sorted as follows:

ID
5
10
6
7
8
Instead Of
ID
5
6
7
8
10.


Any help would be great.

Thanks
Dan

Hi LuisMartin,
It's an Integer column. Thanks

Here is the code sample.
SELECT
pe.login_name,
we.application_id,
we.win_export_id,
we.enrollment_status_id,
es.status_name,
p.packet_id,
p.name [packet_name],
we.date_created,
we.date_modified
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id

This query returns multiple number of records with multiple Package_IDs including
(5
6
7
8
10)
associated with that records which need to be sorted as
5
10
6
7
8

Thanks
Dan

Hi LuisMartin,
Sorry there is a misunderstanding here. My question here was how can I sort that way for Packet_ID? I know we can sort in ASC or DESC order but it should be sorted as specified order which I can't get to figure it out. Thanks

Packet_ID
5
10
6
7
8

Dan

I think I got it. I added an additional column which sorts these ids based on case statement. Thanks for your time LuisMartin.

Dan

Dan,

To be honest, that's really an unordered order.

The IDs are probably ordered by another column in the table, or by a column in the table referenced by one of the FKs on your table.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br />To be honest, that's really an unordered order.<br />The IDs are probably ordered by another column in the table, or by a column in the table referenced by one of the FKs on your table.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Sorry.. Could you please explain what you meant by that <img src='/community/emoticons/emotion-5.gif' alt='' /> ?<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br />To be honest, that's really an unordered order.<br />The IDs are probably ordered by another column in the table, or by a column in the table referenced by one of the FKs on your table.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Sorry.. Could you please explain what you meant by that <img src='/community/emoticons/emotion-5.gif' alt='' /> ?<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">What I meant was that perhaps the order in which these IDs are shown, which is not in any recognizable order <img src='/community/emoticons/emotion-5.gif' alt='' />, reflects the order of a different column. Perhaps the ID is the first column of the resultset, and if the original poster would check the other columns he might see a column that IS ordered in a recognizable fashion.

OP Said<br /> " I added an additional column which sorts these ids based on case statement"<br /><br />I Interpret this as<br /><br />CREATE TABLE #Test(id int)<br />INSERT INTO #Test VALUES(5)<br />INSERT INTO #Test VALUES(10)<br />INSERT INTO #Test VALUES(6)<br />INSERT INTO #Test VALUES(7)<br />INSERT INTO #Test VALUES(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />SELECT Id, CASE id WHEN 5 THEN 1<br /> WHEN 10 THEN 2<br /> ELSE id END AS SortCol<br />FROM #Test<br />ORDER BY SortCol<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

Well, no need to add it as a column in the select list: putting the CASE statement itself in the ORDER BY clause will do the job.

It's still an arbitrary order, in that there is no 'trend' in the order of the numbers. So yes, you can reproduce this exact sequence of numbers with a CASE statement, but you shouldn't call it an ordered resultset.

<br />Cant resist <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Only the query that I posted satisfies the following statement.<br /><br />&lt;quote&gt;<br />I <b>added</b> an <b>additional column </b> which sorts these ids based on <b>case statement</b><br />&lt;/quote&gt;<br /><br />[<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

Thanks all for your comments and suggestions. I know we can sort as ASC or DESC but the FE wanted to be this way specifically (you can say hardcoded). I think Roji got the right solutions in this case, [<img src='/community/emoticons/emotion-1.gif' alt='' />]. And this is how I did it. <br /><br />SELECT <br />pe.login_name, <br />we.application_id, <br />we.win_export_id, <br />we.enrollment_status_id, <br />es.status_name, <br />p.packet_id, <br />p.name [packet_name], <br />we.date_created, <br />we.date_modified,<br />CASE p.Packet_ID<br />WHEN 10 THEN 6<br />ELSE p.Packet_ID <br />END AS SortedPacketID<br />FROM Table1 we <br />JOIN Table2 ap ON we.application_id = ap.application_id <br />JOIN Table3 pe ON ap.person_id = pe.person_id <br />LEFT JOIN Table4 es <br />ON we.enrollment_status_id = es.enrollment_status_id <br />LEFT JOIN Table5 p ON we.packet_id = p.packet_id<br />ORDER BY SortedPacketID ASC<br /><br /><br />IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks<br /><br />Dan<br />

quote:Originally posted by Bredsox
IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

Try this.

SELECT * --Use Column list
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es
ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id
ORDER BY PacketID % 5 ASC


Roji. P. Thomas
http://toponewithties.blogspot.com

Roji,
It's not giving me the right order. This what your script is generating:

ID
10
6
7
8
5


Thanks
Dan

I already mentioned that you don't have to put the CASE expression in the select list. Just use it as the ORDER BY clause.

Adriaan,<br /> Can you give me an example of how can I put in Order By with that specific requiremnt (4 10 6 7 <img src='/community/emoticons/emotion-11.gif' alt='8)' />? ASC/DESC? It won't work because I want 4 10 6 7 8 INSTEAD OF (4 6 7 8 10 ASC OR 10 8 7 6 4 DESC).<br /><br /><br />Dan

quote:Originally posted by Bredsox

Roji,
It's not giving me the right order. This what your script is generating:

ID
10
6
7
8
5


Thanks
Dan

How would it give a 5 if the original was a 4?

Your values, 4, 10, 6, 7, 8 do have a pattern, even though they are not intuitive. they would sort if the result was allowed to be for any n (n + 1) div 5.

That would be
(4 + 1) div 5 = 0
(10 + 1) div 5 = 1
(6 + 1) div 5 = 2
(7 + 1) div 5 = 3
(8 + 1) div 5 = 4

If div does not work, it can be done as (n + 1) - (5 * int((n+1)/5), I guess.

So, in this sample,
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15

would sort out as

4, 9, 14, 5, 10, 15, 1, 6, 11, 2, 7, 12, 3, 8, 13

Is that what you are looking for? Without a pattern, we are all trying to just gess how to get a quick sort to work for you...

Hi Aefager,
Your logic works pretty cool and certainly a good reference for me. In this case, they don't want the result to be like that. They just wanted hardcoded like 4 and then 10 and 6 and 7 and 8 and so on. sorry for the confusion.
The actual data should be sorted as 4 10 6 7 8. 5 was just an example and actually with that example Roji's sorting without additional column worked but when I ran that script in the real data (4 6 7 8 10...) it did not give me 4 in the first place instead it gave me in the last place like this: 10 6 7 8 4. I just wanted to check if it can be done without additional column, otherwise it is already been deployed and looks like working for FE application. Thanks


Dan

quote:Originally posted by Roji. P. Thomas

quote:Originally posted by Bredsox
IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

Try this.

SELECT * --Use Column list
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es
ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id
ORDER BY PacketID % 5 ASC


Roji. P. Thomas
http://toponewithties.blogspot.com

What do I change if the numbers are 4 6 7 8 10... ? to get 4 10 6 7 8.

SELECT * --Use Column list
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es
ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id
ORDER BY (PacketID+1) % 5 ASC

quote:Originally posted by Bredsox

quote:Originally posted by Roji. P. Thomas

quote:Originally posted by Bredsox
IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

Try this.

SELECT * --Use Column list
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es
ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id
ORDER BY PacketID % 5 ASC


Roji. P. Thomas
http://toponewithties.blogspot.com

What do I change if the numbers are 4 6 7 8 10... ? to get 4 10 6 7 8.

Roji. P. Thomas
http://toponewithties.blogspot.com

quote:Originally posted by Roji. P. Thomas

SELECT * --Use Column list
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es
ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id
ORDER BY (PacketID+1) % 5 ASC

quote:Originally posted by Bredsox

quote:Originally posted by Roji. P. Thomas

quote:Originally posted by Bredsox
IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

Try this.

SELECT * --Use Column list
FROM Table1 we
JOIN Table2 ap ON we.application_id = ap.application_id
JOIN Table3 pe ON ap.person_id = pe.person_id
LEFT JOIN Table4 es
ON we.enrollment_status_id = es.enrollment_status_id
LEFT JOIN Table5 p ON we.packet_id = p.packet_id
ORDER BY PacketID % 5 ASC


Roji. P. Thomas
http://toponewithties.blogspot.com

What do I change if the numbers are 4 6 7 8 10... ? to get 4 10 6 7 8.

Roji. P. Thomas
http://toponewithties.blogspot.com

Worked like a charm but can you please explain how it sorted that way. What does the (Packet_ID+1) % 5 ASC is doing? Thanks Roji and everyone for your time.

Dan

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Your values, 4, 10, 6, 7, 8 do have a pattern, even though they are not intuitive. they would sort if the result was allowed to be for any n (n + 1) div 5.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Any finite list of numbers has a pattern/function that can be expressed using polinom. [<img src='/community/emoticons/emotion-1.gif' alt='' />]

Uh, old reflexes haven't died yet. [<img src='/community/emoticons/emotion-1.gif' alt='' />]