Ordering query results as specified in the where clause
I have a table QuotesTable
- a primary key quotesid
.
I have this sql-statement
:
select * from QuotesTable where quotesid in (103,7,16,50,41,80,67,64)
This returns me the result in the following order:
7
16
41
.........
103 and so on.
but I need the results in the following order as stated in the query (103,7,16,50,41,80,67,64) as:
103,
7
16
.......
64 and so on.
Is there a way to achieve this?
+3
source to share
2 answers
Try the following:
select * from QuotesTable where quotesid in (103,7,16,50,41,80,67,64)
order by case quotesid when 103 then 1
when 7 then 2
when 16 then 3
when 50 then 4
when 41 then 5
when 80 then 6
when 67 then 7
when 64 then 8
end
If these values grow, you can create a table in the database:
create table QuotesOrderingTable(quotesid int, orderid int)
go
fill it in with the appropriate values:
insert into QuotesOrderingTable values
(103, 1),
(7, 2),
(16, 3),
(50, 4),
(41, 5),
(80, 6),
(67, 7),
(64, 8),
(..., 9),
(..., 10),
...
and then use it for ordering:
select qt.* from QuotesTable qt
join QuotesOrderingTable qot on qt.quotesid = qot.quotesid
where qt.quotesid in (103,7,16,50,41,80,67,64)
order by qot.orderid
+4
source to share
Another option does it like this:
SELECT 1, * FROM QuotesTable WHERE quotesid = 103 UNION ALL
SELECT 2, * FROM QuotesTable WHERE quotesid = 7 UNION ALL
SELECT 3, * FROM QuotesTable WHERE quotesid = 16 UNION ALL
SELECT 4, * FROM QuotesTable WHERE quotesid = 50 UNION ALL
SELECT 5, * FROM QuotesTable WHERE quotesid = 41 UNION ALL
SELECT 6, * FROM QuotesTable WHERE quotesid = 80 UNION ALL
SELECT 7, * FROM QuotesTable WHERE quotesid = 67 UNION ALL
SELECT 8, * FROM QuotesTable WHERE quotesid = 64
ORDER BY 1
Not the prettiest, but at least you don't repeat the WHERE clause
Another variation that looks a little nicer:
SELECT * FROM QuotesTable q
JOIN (SELECT 1 ordering, 103 quotesid UNION ALL
SELECT 2 , 7 UNION ALL
SELECT 3 , 16 UNION ALL
SELECT 4 , 50 UNION ALL
SELECT 5 , 41 UNION ALL
SELECT 6 , 80 UNION ALL
SELECT 7 , 67 UNION ALL
SELECT 8 , 64) o ON o.quotesid = q.quotesid
ORDER BY o.ordering
0
source to share