distinct using Group by

rahul678 · #1 (**permalink**) 01-13-12, 02:46

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.

r937 · #2 (**permalink**) 01-13-12, 03:03

Quote:

Originally Posted by rahul678

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

tonkuma · #3 (**permalink**) 01-13-12, 03:35

Quote:

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
;

rahul678 · #4 (**permalink**) 01-13-12, 04:34

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)

tonkuma · #5 (**permalink**) 01-13-12, 05:23

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).

rahul678 · #6 (**permalink**) 01-13-12, 05:38

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.

tonkuma · #7 (**permalink**) 01-13-12, 06:27

Quote:

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).

Quote:

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.)

rahul678 · #8 (**permalink**) 01-13-12, 06:28

Third example fetched me the result. Thanks Tonkuma. You Rockkkkkkkkkkkkkkkkkkkk

Internet Services

Web Development

Digital Marketing

Consumer Tech