sql getting information, but in wrong order

Question

I have the following query in sql:

SELECT 
a1 
FROM 
dbo.myProductNames 
WHERE
 keycode = 40  OR keycode = 28 OR keycode = 32 OR keycode = 50

a1 = the name

I need the information in the table to output the names its queried in the order ive asked, so I need 40 to be first then 28 so on on.

The reason for this is my code reads the results and stores in an array that then is used to display on a form.

The table that the SQL query comes from has the information stored in num order, so 28 will be read first etc etc.

As I have said I need to get the information in the order I entered in the where clause.

I'm thinking something to do with nested Select statements although ive never tried nested selects.

Or, is there an easy way?

Any help would be great!

Many thanks in advance!

Update:

ok here is the solution thanks to you guys!

SqlCommand pgNameFill = new SqlCommand("SELECT  a1, CASE keycode WHEN @pg1 THEN 1 WHEN @pg2 THEN 2 WHEN @pg3 THEN 3 WHEN @pg4 THEN 4 END AS SortOrder FROM  dbo.myProductNames WHERE keycode IN (@pg1, @pg2, @pg3, @pg4) ORDER BY SortOrder ASC", conny1);
        
        pgNameFill.Parameters.AddWithValue("@pg1", pg1);
        pgNameFill.Parameters.AddWithValue("@pg2", pg2);
        pgNameFill.Parameters.AddWithValue("@pg3", pg3);
        pgNameFill.Parameters.AddWithValue("@pg4", pg4);
        SqlDataReader readpg = pgNameFill.ExecuteReader();

Many thanks for all the posts! I wish I could flag more answers than just one, oh well upvotes all around!

By the way you could also use WHERE keycode IN(40,28,32,50) and you seem to be looking for some kind of ORDER BY ? — Ruben, Dec 12 '11 at 12:33
Based on a couple of comments that you've made, you state that this will be parametrised. Will it be that you have a fixed number of integer parameters? Or do you need a solution that accepts 'any number' of parameters, and still maintains the order? Then, will it be dynamic sql (building up a sql string to execute, with the parameters referenced in that string), or a stored procedure? Also, which version of SQL is this for? — MatBailie, Dec 12 '11 at 12:48
ah yes, it will always be 4 params, im using MSSQL on a 2008 server. and i will use the select in a string attached to my code in c# — lemunk, Dec 12 '11 at 12:56
@Steven Smith: In your SqlCommand, `WHEN pg4 THEN 4` should be `WHEN @pg4 THEN 4`. — , Dec 12 '11 at 13:13
See also this related (but newer) [question](http://stackoverflow.com/questions/10999913/why-do-results-from-a-sql-query-not-come-back-in-the-order-i-expect). — senderle, Jul 07 '12 at 22:04

score 5 · Answer 1 · answered Dec 12 '11 at 12:37

5

One way (assuming mssql from your history);

;with myRank(rank, keycode) as (
    select 1, 40 union
    select 2, 28 union
    select 3, 32 union
    select 4, 50
)
select 
    a1
from 
    myProductNames 
    inner join myRank on (myProductNames.keycode = myRank.keycode)
order by
    myRank.rank asc

answered Dec 12 '11 at 12:37

Alex K.

171,639
30
264
288

that looks interesting, there is one other thing though looking at what you have done, my values will be parameters passed so it will be keycode = @pg1. is this ok to to pass parameters like this or does it have to be done differently? – lemunk Dec 12 '11 at 12:39
It depends on what exactly is variable, you can use variables in the cte as in `select 1, @1stparam_with_top_rank union ...` – Alex K. Dec 12 '11 at 12:42
Personally I would avoid hard-coding this kind of logic into the query. What happens when it needs to change? Could be a maintenance headache. – Simon Dec 12 '11 at 12:50
1

Assuming there are always 4 params and you want them sorted in the order of 1 to 4 you can replace the fixed keycode with `select 1, @pg1 union select 2, @pg2 ...` – Alex K. Dec 12 '11 at 13:16

score 5 · Accepted Answer · answered Dec 12 '11 at 12:44

5

Add the following ORDER BY clause:

ORDER BY
CASE keycode 
    when 40 then 1
    when 28 then 2
    when 32 then 3
    when 50 then 4
END

answered Dec 12 '11 at 12:44

score 4 · Answer 3 · answered Dec 12 '11 at 12:33

4

You need to use the ORDER BY keyword. Unless an order is specified in the query there is no guarantee in which order the database will return records. The order of records in the table will not necessarily be the order of records in the query results.

If the order you require is not strictly numerical or alphabetical you may find it necessary to add an additional SortOrder column to your table which you can then use to sort as desired.

answered Dec 12 '11 at 12:33

Simon

6,062
13
60
97

2

but order by what? he wants to order by keycode, but not ASC or DESC but in a specific way for each query (if i understand correctly). – Flo Dec 12 '11 at 12:37
i agree and normally this would be my first attempt at solving this minor issue. But the order is what ever the user wish's so there may not be an order, thats the biggest problem i have – lemunk Dec 12 '11 at 12:41
exactly right flo, as i said this is the annoying problem set by the user hehe, if i had my way i would just sort the info ASC or DESC and there would be no probs! – lemunk Dec 12 '11 at 12:43
I don't think there is any easy way around it without hardcoding a lot of logic in the query. If the user wants to be able to sort in an arbitrary way then you probably need to store that in the database in an additional column. – Simon Dec 12 '11 at 12:47
yes, some suggestions have suggested a case assigning an ID to each value. – lemunk Dec 12 '11 at 12:49

score 2 · Answer 4 · answered Dec 12 '11 at 12:39

2

Adding to Simon's answer. I would change the query to something like:

SELECT  
a1,
CASE keycode WHEN 40 THEN 1
WHEN 28 THEN 2
WHEN 32 THEN 3
WHEN 50 THEN 4 END AS SortOrder
FROM  dbo.myProductNames  
WHERE keycode IN (40, 28, 32, 50)
ORDER BY SortOrder ASC

answered Dec 12 '11 at 12:39

anothershrubery

20,461
14
53
98

excellent answer, but my question to rebound is what about parameters. In my code the user selects a "pg"(product group) which could be 40 etc. This is then passed as a parameter in an sql reader in my c# code like this "WHERE keycode = @pg1 OR keycode = @pg2....etc" . Only reason i ask is because if i remember there is always some restrictions when using parameters. – lemunk Dec 12 '11 at 12:46

score 1 · Answer 5 · answered Dec 12 '11 at 12:45

1

SELECT a1
 FROM myProductNames
 ORDER BY CASE WHEN keycode =40 THEN 1
               WHEN keycode =28 THEN 2
               WHEN keycode =32 THEN 3
               WHEN keycode =50 THEN 4
               ELSE 5
               END;

answered Dec 12 '11 at 12:45

Glenn

8,932
2
41
54

sql getting information, but in wrong order

5 Answers5