Predefined Ordering in 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

SQL Server 2000 Forums

Transact-SQL (2000)

Predefined Ordering in Order By

Author

Topic

Mohammed Lokhandwala
Starting Member

4 Posts

Posted - 2005-11-01 : 04:23:28

I have a table called Resources. The table has the following column
id,
name
currentcount
maxcount

I want to query the table such that the result is order by the sequence of ID that i specify somehow.

Example:
Assume 10 records with IDs 1 to 10.

I want to write a query similar to

Select * from Resources where currentcount+5 < maxcount and ID in (5,3,9) order by 9,3,5

obviously the order by in the above statement is illegal, but thats the result i am looking for. Thus if each of the 3 records with IDs 3, 5, and 9 satify the where clause then the resultset should have row with ID 9 first, followed by 3 and then by 5.

Hope someone has a solution for this. Currently implemented as a programmatic loop and query for each ID individually.

Mohammed

DBud

Kristen
Test

22859 Posts

Posted - 2005-11-01 : 05:13:45

Hi Mohammed Lokhandwala, Welcome to SQL Team!


ORDER BY 
    CASE [ID]
        WHEN 9 THEN 1
        WHEN 3 THEN 2
        WHEN 5 THEN 3
        ELSE 999 -- Everything else at the end!
        END

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-01 : 06:51:05

you can also use
order by charindex(',' + id + ',', ',5,3,9,') DESC

',5,3,9,' is the user defined order string where the first element that appers in the ordered group the last in the string

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 01:15:11

>>charindex(',' + id + ',', ',5,3,9,') DESC

Mladen, I tried yours but couldnt get correct answer


declare @t table(i int)
insert into @t 
select 234 union all select 5 union all select 9 union all select 3 union all 
select 6784 union all select 8 

select * from @t order by case when i=5 then 1 when i=3 then 2 when i=9 then 3 else 999 end

select * from @t order by charindex(',' + cast(i as varchar) + ',', ',5,4,9,') DESC

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-02 : 02:41:20

[code]
declare @t table(i int)
insert into @t
select 234 union all select 5 union all select 9 union all select 3 union all
select 6784 union all select 8

select * from @t order by case when i=5 then 1 when i=3 then 2 when i=9 then 3 else 999 end

-- elements in the string must me reversed because it's ordered by max charindex
select * from @t order by charindex(',' + cast(i as varchar) + ',', ',9,3,5,') DESC
[/code]

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 03:22:13

Well. I didnt notice the reverse order

Madhivanan

Failing to plan is Planning to fail

Mohammed Lokhandwala
Starting Member

4 Posts

Posted - 2005-11-08 : 02:45:32

Thanks Kristen, Spirit1 and madhivan. I really like both approaches. I have not decided which one to use yet, but I might go for the "Case" approach since its easier for posterity to read, and it might be lighter on the DB CPU!

Once again, Thanks everyone!

DBud

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-08 : 04:08:20

well since both of these queries return same execution plans
i prefer to use the charindex method. once you understand it it all just a matter of adding
new order items into the string instead of writing more cases...
i had a task to order a column on predefined order which had 100 predefined order strings...
coding 100 cases was imediatly out of the picture...

this proved to be equaly cool method.


declare @sortOrder varchar(8000)
set @sortOrder = ',alfki,vinet,tomsp,chops,ricsu,'
select * from orders
order by charindex(',' + customerid + ',', @sortOrder) DESC

select * from orders
order by 
    CASE customerid
        WHEN 'ricsu' THEN 1
        WHEN 'chops' THEN 2
        WHEN 'tomsp' THEN 3
	WHEN 'alfki' THEN 4
	WHEN 'vinet' THEN 5
        ELSE 999 -- Everything else at the end!
        END

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-08 : 08:27:39

The most important question here is: Is the ordering ad-hoc, different for each run and something that changes, or is it (or should it be) permanent? If it is ad-hoc, the methods presented will work. But if what you are *really* after is a way to make sure that Reource X always sorts AFTER Resource Y, then you should store sort values in your table defining the resources and order by those. Again, depends on your needs.

I just want to point this out, because often the latter is what people really are after even though at first they *think* it needs to be specified dynamically.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 08:44:26

quote:
...and ID in (5,3,9) order by 9,3,5

...leads me to think that ID is a key in another table (RESOURCES, presumably), and that you should store your SortOrder as a column in that table. Both the solutions presented so far have been static, and I'd rather put a sort order such as this into a table than do any hard-coding of data.

Mohammed Lokhandwala
Starting Member

4 Posts

Posted - 2005-11-08 : 09:17:59

JSmith/Blindman,

The ordering requirement is ad-hoc. i cannot predefine it. The logic is something like this.
If User chooses X, pick resources in order 1,2,3 only.
If User chooses Y, pick resources in order 2,1,3 only. etc.

DBud

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 09:31:29

Then you need a table of ID values, Choices, and SortOrder.

Mohammed Lokhandwala
Starting Member

4 Posts

Posted - 2005-11-08 : 09:48:32

Blindman,

Note that Resources table is used by other aspects of the system and cannot be changed. The component that reads the resources table is logically only supposed to look at the resources table and cannot join it with any other table without compromising its independence.

DBud

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 10:52:36

quote:
Note that Resources table is used by other aspects of the system and cannot be changed.

No problem. You will need a separate table to store multiple sort orders anyway.

quote:
The component that reads the resources table is logically only supposed to look at the resources table and cannot join it with any other table without compromising its independence.

What the hell does that mean? Use a view to join the tables if you must.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources