DB2 Query to get certain type of records first

sri_mf

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.

Suresh Ponnusamy · Posted: Tue Aug 05, 2008 6:54 pm Post subject:

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.

die7nadal · Active User Joined: 23 Mar 2005 Posts: 154

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.

sri_mf

Thank you Suresh and Nadal for your replies.