ORDER BY - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.
You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
David Portas
SQL Server MVP
|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:

> SouRa wrote:
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.