CODEWITHSUNDEEP
sql-server-2008
Venkat
Venkat

Reputation: 2156

ordering the results of a query as specified in the where clause

I have a table QuotesTable - primary key is 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 specified 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 one?

Upvotes: 3

Views: 62

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try this:

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 those values grow then you can create a table in database:

create table QuotesOrderingTable(quotesid int, orderid int)
go

fill it with 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 to order by:

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

Upvotes: 4

FuzzyJulz
FuzzyJulz

Reputation: 2874

Another option is doing 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 atleast you aren't repeating the WHERE clause

Another variation, which looks a bit 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

Upvotes: 0

Related Questions