Results 1 to 5 of 5
  1. #1
    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

    Thanks in advance
    Sbdash

  2. #2
    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


    Output received is ...

    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

    Thanks in advance
    Sbdash
    Oracle can do wonders !

  3. #3
    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. #4
    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
    Oracle can do wonders !

  5. #5
    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
  •