|
View previous topic :: View next topic
|
| Author |
Message |
sri_mf
Active User
Joined: 31 Aug 2006
Posts: 218
Location: India
|
|
|
|
I have a table containing two columns C1 and C2.
C1
C
A
B
C
D
A
B
D
C
and C2 Contains some numeric data.
Now the required output should be like this
C1
C
C
C
A
A
B
B
D
D
Ie All the 'C' Type records first and then remaining records on sorted order.
Can anyone provide query for this scenario. |
|
| Back to top |
|
 |
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi Sri,
Please try the below given.
TABLE - TABLE1
COLUMN - C1 AND C2
SELECT 1, A.C1, A.C2
FROM TABLE1 A
WHERE A.C1 = 'C'
UNION ALL
SELECT 2, B.C1, B.C2
FROM TABLE1 B
WHERE B.C1 <> 'C'
ORDER BY 1 ASC, 2 ASC
This query will give the result as follows
1 C1
-------------------------
1 C
1 C
1 C
2 A
2 A
2 B
2 B
2 D
2 D
If required, You can select column C2 alongwith C1. |
|
| Back to top |
|
|
die7nadal
Active User
Joined: 23 Mar 2005
Posts: 156
|
|
|
|
If you can make sure C1 doesn't contain X'00', then you can use this query that uses just one pass of the table.
| Code: |
SELECT C1,
C2,
CASE WHEN C1='C' THEN X'00' ELSE C1
END AS SORT_COL
FROM TABLE1
ORDER BY 3
WITH UR; |
|
|
| Back to top |
|
|
sri_mf
Active User
Joined: 31 Aug 2006
Posts: 218
Location: India
|
|
|
|
| Thank you Suresh and Nadal for your replies. |
|
| Back to top |
|
|
|
|
