# Thread: Sorting in a particular manner

1. Registered User
Join Date
Dec 2003
Location
India
Posts
12

## Unanswered: Sorting in a particular manner

Hi All,
I have table say TAB the columns and data in this table are as follows

ALK STATUS POSTCODE
1 C CF116LR
1 D CF116LR
2 C CF116LR
3 D CF116LR

If i query from this table i want the result should be sorted on the basis of the following priority
Priority 1) If in the result set for the same alk both C and D address are present then they should be dispalyed first
Priority 2) If no C and D combination exists for the same alk then the addresses having only C records should be dispalyed
Priority 3)If no C addreses are there then addresses having only D status should be dispalyed.
For example
if i give select * from tab where postcode='CF116LR';
the result set should be in the following manner
1 C CF116LR
1 D CF116LR
2 C CF116LR
3 D CF116LR

If alk 1 were not preset in the table the result set should be
2 C CF116LR
3 D CF116LR

Sbdash

2. Registered User
Join Date
Dec 2003
Location
Noida, India.
Posts
171

## Re: Sorting in a particular manner

I can figure out that:
1. When both C and D are present, we want to show both with C appearing first.
2. When only C is present, we show only C
3. When only D is present, we show only D

In this case, simply sorting on alk and then on status with a where condition of status in ('C','D') should work.

like in:

select alk, status, postcode
from t1
where status in ('C','D')
order by alk, status

ALK S POSTCODE
---------- - ---------
1 C CF116LR
1 D CF116LR
2 C CF116LR
3 D CF116LR

... which is is what you require!

In case what you require is different from what I understood, pls explain

Originally posted by sbdash
Hi All,
I have table say TAB the columns and data in this table are as follows

ALK STATUS POSTCODE
1 C CF116LR
1 D CF116LR
2 C CF116LR
3 D CF116LR

If i query from this table i want the result should be sorted on the basis of the following priority
Priority 1) If in the result set for the same alk both C and D address are present then they should be dispalyed first
Priority 2) If no C and D combination exists for the same alk then the addresses having only C records should be dispalyed
Priority 3)If no C addreses are there then addresses having only D status should be dispalyed.
For example
if i give select * from tab where postcode='CF116LR';
the result set should be in the following manner
1 C CF116LR
1 D CF116LR
2 C CF116LR
3 D CF116LR

If alk 1 were not preset in the table the result set should be
2 C CF116LR
3 D CF116LR

Sbdash

3. Registered User
Join Date
Dec 2003
Location
India
Posts
12
Yes you are right if we sort on the basis of alk first and then on status we would get the result only in somes cases for example

alk status
1 C
1 D
2 C
3 D
in this case the above sorting process will work but concider the following case
alk status
1 C (note no D address is present here)
2 D
2 C
3 D

so here if we sort on alk first and then on status we would get a result set like

1 C
2 C
2 D
3 D
which is wrong as a D anc C combination is present for alk 2 so 2 should be present ahead of 1 in the result set...the required result set should be
2 C
2 D
1 C
3 D
I need those alk which have C and D combination first then those alk which have only C status and finally only D sets
Thanks
Sbdash

4. Registered User
Join Date
Dec 2003
Location
Noida, India.
Posts
171
The folllowing query will give the desired output:

select * from t1;
A S
- -
1 C
2 C
2 D
3 D
4 C

select a.alk, b.status from
(select alk, sum(Present) sumPresent from
(select alk, decode(status,'C',2,'D',1,0) present from t1)
group by alk ) a, t1 b
where a.alk=b.alk
order by a.sumPresent desc
A S
- -
2 C
2 D
1 C
4 C
3 D

Originally posted by sbdash
Yes you are right if we sort on the basis of alk first and then on status we would get the result only in somes cases for example

alk status
1 C
1 D
2 C
3 D
in this case the above sorting process will work but concider the following case
alk status
1 C (note no D address is present here)
2 D
2 C
3 D

so here if we sort on alk first and then on status we would get a result set like

1 C
2 C
2 D
3 D
which is wrong as a D anc C combination is present for alk 2 so 2 should be present ahead of 1 in the result set...the required result set should be
2 C
2 D
1 C
3 D
I need those alk which have C and D combination first then those alk which have only C status and finally only D sets
Thanks
Sbdash

5. Registered User
Join Date
Dec 2003
Location
India
Posts
12
Thanks a lot.

Sbdash

Originally posted by cmasharma
The folllowing query will give the desired output:

select * from t1;
A S
- -
1 C
2 C
2 D
3 D
4 C

select a.alk, b.status from
(select alk, sum(Present) sumPresent from
(select alk, decode(status,'C',2,'D',1,0) present from t1)
group by alk ) a, t1 b
where a.alk=b.alk
order by a.sumPresent desc
A S
- -
2 C
2 D
1 C
4 C
3 D

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•