How to ORDER BY an unordered list

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)

How to ORDER BY an unordered list

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2004-08-27 : 13:21:03

This probably sounds strange, but bear with me.

I need to return a small list of records that should be ordered by the order list I supply. i.e. not by their relative values.

The following is 'wish code' to give an idea of what I mean:

e.g. SELECT ColourName,ColourID FROM MyColours ORDER BY ColourID (4,3,2,5,1)

Anyone ever needed to order by a non-ordered list? If so, what did you do?

Thanks
Mark

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 13:28:00

will this help?

SELECT ColourName,ColourID
FROM MyColours
ORDER BY case ColourID when 4 then 1,when 3 then 2, when 2 then 3, when 5 then 4, when 1 then 5 end

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-27 : 13:32:48

[code]
USE Northwind
GO

CREATE TABLE myTable99(ColorID int IDENTITY(1,1), ColorName varchar(50))
GO

INSERT INTO myTable99(ColorName)
SELECT 'Red' UNION ALL
SELECT 'Yellow' UNION ALL
SELECT 'Pink' UNION ALL
SELECT 'Green' UNION ALL
SELECT 'Purple'
GO

SELECT ColorName,ColorID
FROM MyTable99
ORDER BY CASE ColorId
WHEN 4 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
WHEN 5 THEN 4
WHEN 1 THEN 5
ELSE 6
END
GO

DROP TABLE myTable99
GO

[/code]

Or create a table with the order abd join to it based on the id, an order by your order column

That would be more bynamic and more flexible

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-27 : 13:37:30

Damn...sniped

Ok then..here's the table driven one


USE Northwind
GO

CREATE TABLE myTable99(ColorID int IDENTITY(1,1), ColorName varchar(50))
GO

INSERT INTO myTable99(ColorName)
SELECT 'Red'	UNION ALL
SELECT 'Yellow' UNION ALL
SELECT 'Pink'   UNION ALL
SELECT 'Green'  UNION ALL
SELECT 'Purple'
GO

CREATE TABLE myOrder99 (IDCol int, OrderCol int)
GO

INSERT INTO myOrder99(IDCol, OrderCol)
SELECT 4,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 5,4 UNION ALL
SELECT 1,5
GO

  SELECT ColorName,ColorID 
    FROM MyTable99 JOIN myOrder99
	ON ColorId = IDCol 
ORDER BY OrderCol
GO

DROP TABLE myTable99
DROP TABLE myOrder99
GO

Brett

8-)

mark1504
Posting Yak Master

103 Posts

Posted - 2004-08-28 : 06:51:42

Thanks guys.

I'll try the UNION method. I forgot to mention that the number of items and their values will vary so I've have to achieve this with variables.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-29 : 18:58:17

quote:
Originally posted by X002548

ORDER BY CASE ColorId
WHEN 4 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
WHEN 5 THEN 4
WHEN 1 THEN 5
ELSE 6
END
GO

DROP TABLE myTable99
GO

Or create a table with the order abd join to it based on the id, an order by your order column

That would be more bynamic and more flexible

Brett

8-)

Can you please explain this code to me.
Thanks

mk_garg

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-30 : 11:39:39

Just cut and paste the code and you'll see it run, but it simply reorders the result set based on the requirement...

I'd go with the table..

Brett

8-)

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-30 : 18:39:08

This works fine.
WHEN 4 THEN 1
WHEN 3 THEN 2

How is it working?
Are we saying, if colorid is 4 make this first item is list and if colorid is 3 make this second item in the list.

Thanks

mk_garg

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 09:23:03

imagine it like this:
it kind of adds a column to order by.
case
when 4 then 1 - set the virtual column to 1
when 3 then 2 - set the virtual column to 2
...
end
it sorts ASC since it is a default.

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

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 18:25:36

Thanks Spirit!

Now i got what you guys are doing in query.

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources