CODEWITHSUNDEEP
sqlsql-server-2008
Sam
Sam

Reputation: 3167

Sql query - distinct groups

I have a table called TableA.

Input:

ColumnA      ColumnB    ColumnC
jim          1788           5F
jim          2000           9F
jim          500            9F
ben          190            4H
geri          40        manny
geri          40        kelly
geri          20        joker
geri          20        jam

Output:

ColumnA     ColumnB     ColumnC
jim            2000         9F
jim            2000         NULL
ben             190         4H
geri            40          manny
geri            40          kelly
geri            40          NULL

Can someone help me with the SQL query?

The logic is the following:

I want to group the rows by ColumnA, e.g. the "gerri" group will have 4 rows. For each group, the maximum element from ColumnB is calculated , for gerri group it's 40. Each element in the group is analysed for ColumnB: if the element.ColumnB = maximum, the row is put in the output (if it doesn't exist already). Otherwise, if the element.ColumnB different than the maximum, the current row is put in the output with NULL on ColumnC and MAXIMUM on ColumnB (again, if it doesn't exist in the output already).

It's clearer from the example I gave.

Thank you for any suggestions in advance!

Upvotes: 0

Views: 70

Answers (4)

Madhivanan
Madhivanan

Reputation: 13700

Something like this may work

with cte (columna,columnb)
as
( 
select columna,max(columnb) as columnb from table
group by  columna
)

select t1.columna,t1.columnb,t1.columnc from table as t1 inner join cte as ct 
on t1.columna=t2.columna and t1.columnb=t2.columnb
union all
select columa,columnb,NULL from cte

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

; with maxes as (
  select ColumnA,
         ColumnB,
         ColumnC,
         max(ColumnB) over (partition by ColumnA) mx
    from tablea
)
select distinct
       ColumnA, 
       mx ColumnB, 
       case when mx = ColumnB 
            then ColumnC 
            else null 
        end ColumnC
from maxes

Example on Sql Fiddle.

Upvotes: 3

Ruzbeh Irani
Ruzbeh Irani

Reputation: 2438

WITH CTE AS 
(
    select   ColumnA,MAX(ColumnB) as max
    FROM    TABLE 
)select ColumnA,b.max,CASE WHEN a.ColumnB=b.max  THEN ColumnC ELSE NULL END
FROM TABLE  a
INNER JOIN CTE b on a.ColumnA=b.ColumnA

Upvotes: 0

Ian P
Ian P

Reputation: 1724

Look up this article on subqueries: it will help you with this and much else in the future:

http://allenbrowne.com/subquery-01.html

Upvotes: 1

Related Questions