Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    16

    Unanswered: distinct using Group by

    Hi,

    When I execute the below query I'm getting duplicates in Column1. If I use distinct(column1) its throwing an error that cant use distinct when we are using group by.

    select a.column1,a.column2 from siebel.table1 a, siebel.table2 b where a.column3=b.column3 and a.column4 in (select max(a.column4) from siebel.table1 a group by a.column1)

    Please help me on this.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rahul678 View Post
    If I use distinct(column1) ...
    DISTINCT is ~not~ a function, DISTINCT applies to all columns in the SELECT clause

    Code:
    SELECT a.column1
         , a.column2 
      FROM ( SELECT column1
                  , MAX(column4) AS maxcolumn4
               FROM siebel.table1 
             GROUP 
                 BY column1 ) AS m
    INNER
      JOIN siebel.table1 a
        ON a.column1 = m.column1             
       AND a.column4 = m.maxcolumn4
    INNER
      JOIN siebel.table2 b 
        ON b.column3 = a.column3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DISTINCT is ~not~ a function, DISTINCT applies to all columns in the SELECT clause
    Yes! r937 was right.

    Here are some other examples.

    Example 1: make a subquery to correlated subquery(Add "WHERE am.column1 = a.column1")
    Code:
    SELECT a.column1
         , a.column2
     FROM  siebel.table1 a
         , siebel.table2 b
     WHERE a.column3 = b.column3
       AND a.column4
           = (SELECT MAX(a.column4)
               FROM  siebel.table1 am
               WHERE am.column1 = a.column1
             )
    ;

    Example 2: Move reference to table2 into EXISTS predicate.
    Because, columns of table2 are not imcluded in final select-list.
    Code:
    SELECT a.column1
         , a.column2
     FROM  siebel.table1 a
     WHERE a.column4
           = (SELECT max(a.column4)
               FROM  siebel.table1 am
               WHERE am.column1 = a.column1
             )
       AND EXISTS(
              SELECT 0
               FROM  siebel.table2 b
               WHERE b.column3 = a.column3
           )
    ;

    Example 3: Make one reference to table1 by using ROW_NUMBER OLAP specification.
    Code:
    SELECT a.column1
         , a.column2
     FROM  (SELECT a.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY column1
                                ORDER BY column4 DESC
                          ) rnum
             FROM  siebel.table1 a
             WHERE EXISTS(
                      SELECT 0
                       FROM  siebel.table2 b
                       WHERE b.column3 = a.column3
                   )
           ) a
     WHERE rnum = 1
    ;
    Last edited by tonkuma; 01-13-12 at 07:35. Reason: Remove two blanks in Example 3.

  4. #4
    Join Date
    Nov 2009
    Posts
    16
    Thanks for the replies .......... Sorry I Posted one condition wrong so when I executed the queries I didnt get the desired result. Please check below for the changed where condition. Can anyone help me on this

    select a.column1,a.column2 from siebel.table1 a, siebel.table2 b where a.column1=b.column3 and a.column4 in (select max(a.column4) from siebel.table1 a group by a.column1)

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think no problem by simply changing "b.column3 = a.column3"(or columns in reverse order) to "b.column3 = a.column1" in all examples(of r937 and me).

  6. #6
    Join Date
    Nov 2009
    Posts
    16
    Tonkuma,

    For example1, Inner select query fetches multiple records so If we give = symbol it cant associate multiple values to single value. Inner select query contains Group by and it will fetch many column 4 values.

    For example2: I'm getting the below error message:

    SQL0119N An expression starting with "Column1" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For example1, Inner select query fetches multiple records so If we give = symbol it cant associate multiple values to single value. Inner select query contains Group by and it will fetch many column 4 values.
    But, only MAX(a.column4) was in the SELECT clause.
    So, returned value was one.
    And, I didn't include GROUP BY clause in my examples(that mean group by whole rows selected).

    For example2: I'm getting the below error message:

    ...
    Please publish your complete SQL statement which you executed and error message(s) even if it was same in your last post.
    (Because, I didn't use "Column1"(initial capital) in my examples. So, you must modified something.)
    Last edited by tonkuma; 01-13-12 at 07:30. Reason: Add "And, I didn't include GROUP BY ..." for example 1.

  8. #8
    Join Date
    Nov 2009
    Posts
    16
    Third example fetched me the result. Thanks Tonkuma. You Rockkkkkkkkkkkkkkkkkkkk

Posting Permissions

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