CUSTOMIZE ORDERING IN SQL

Question

1.00/5 (1 vote)

See more:

SQL

SELECT CategoryName
FROM Category WHERE CatId IN (55,568,5412,8991,928,3213,142,9250,12330,2983)

I would like the records to be shown in the same order as (55,568,5412,8991,928,3213,142,9250,12330,2983), but I am getting some random order.

Posted 6-Oct-11 20:06pm

Rakesh S S

Updated 6-Oct-11 20:35pm

André Kraak

v2

Add a Solution

Comments

André Kraak 7-Oct-11 2:35am

Edited question:
Corrected tags
Spelling/Grammar
Removed shouting

Bala Selvanayagam 7-Oct-11 4:13am

Where do you get the series (55,568,5412,8991,928,3213,142,9250,12330,2983)? is it retrieved from an another table ? if so can you please give the table structure of that table and the query you are using ?

If that is the case, this should be possible

let me know

Rakesh S S 7-Oct-11 5:01am

YES CatId coming from some other qry
otherwise it is 2 difficult to get that order

Bala Selvanayagam 7-Oct-11 7:37am

If CatID is comming from an another query on the order (of 55,568,5412,8991,928,3213,142,9250,12330,2983) then why do not you put a join there on Category?

You should give a full detail, if you needs a fast reponse and your issue to be resolved.

3 solutions

Solution 4

This would do it

SQL

SELECT  CategoryName
FROM    Category
JOIN    (
        SELECT 55 AS CatId UNION
        SELECT 568 UNION
        SELECT 5412 UNION
        SELECT 8991 UNION
        SELECT 928 UNION
        SELECT 3213 UNION
        SELECT 142 UNION
        SELECT 9250 UNION
        SELECT 12330 UNION
        SELECT 2983
        ) AS CatFilter
        ON CatFilter.CatId = Category.CatId
ORDER
BY      CatFilter.CatId

Posted 17-Feb-12 0:50am

Costica U

Comments

Photon_ 17-Feb-12 7:27am

a long work but just think about hundred rows

Solution 1

I dont think its possible.

If your table contains primary key then
Sql fetches the records and sort the records based on primarykey column

If your table does not contain primary key then
Sql fetches the records and sort the records based on first column in fetched records.

Posted 6-Oct-11 20:14pm

P.Salini

Comments

Photon_ 17-Feb-12 7:27am

right

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rakesh S S · Accepted Answer · 2011-10-06T20:28:00

Solution 2

i got it

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)
order by case GroupId 
    when 7 then 1 -- First in ordering
    when 14 then 2 -- Second
    else 3
end

Posted 6-Oct-11 20:28pm

Rakesh S S

Updated 6-Oct-11 20:31pm

v2

Comments

Bala Selvanayagam 7-Oct-11 4:17am

Are we going to hard code the numbers in the case statement and what happens if the number series changes ?