Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Unanswered: Need Ordered Distinct value

    Hi,
    I have a table with two columns, Column a is required to be displayed on the order of Column b. Since there is multiple occurance of Col a, I want to show a distinct list of Column a on the order of Column b.

    I did the follwing query.

    select distinct a.column_a from
    ( select column_a, column_b
    from
    table t order by column_b) a

    The distinct values are not showing in the order of Column b. I understand the reason for this also. But is there any other way out ?

    Thanks in advance,
    kaushik mitra

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure I understood what you really want; a simple test case would help. However, I have created it myself. You never mentioned what happens with 'column_b' - does it have distinct values, for example?

    OK, here it is: first, a table and several records:
    Code:
    SQL> select * from test
    
          COLA       COLB
    ---------- ----------
             1         22
             3         45
             5         67
             6         44
             5         32
             3         18
             2          9
    As I've understood your question, this is the situation; in the case that the same 'col_a' has several 'col_b' values, I'll take the "first" one (minimum):
    Code:
    Currently           Desired output
    
    col_a  col_b        col_a  col_b
    -----  -----        -----  -----
    1      22           2       9
    6      44           3      18
    2      9            1      22
    5      67, 32       5      32
    3      45, 18       6      44
    In order to produce such an output, you could use such a query:
    Code:
    SQL> select distinct t1.cola
      2  from test t1
      3  order by (select min(t2.colb) from test t2
      4            where t2.cola = t1.cola
      5           );
    
          COLA
    ----------
             2
             3
             1
             5
             6
    As I've already said, I'm not sure whether this is what you want. If not, provide a sample CREATE TABLE and INSERT INTO statements with the desired output, so that we could see (and, actually, try to code) what you need.

  3. #3
    Join Date
    Jan 2004
    Posts
    12
    Thanks a lot.

    I found an easier way out for this.

    select col_a, max(col_b)
    from t
    group by col_a, order by max(col_b) desc, col_a
    kaushik mitra

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But that is NOT what you asked in the first post:
    I want to show a distinct list of Column a on the order of Column b.
    You didn't say anything about "displaying column_b". Query you wrote suggested that as well:
    Code:
    select distinct a.column_a from
    ...

  5. #5
    Join Date
    Dec 2007
    Posts
    1

    Dis Is Easy Way..

    select distinct deptno,sum(sal)
    from emp
    group by deptno
    order by 2

Posting Permissions

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