CODEWITHSUNDEEP
sqlsql-server-2008
Sam
Sam

Reputation: 3167

Max values on distinct groups

Column1 Column2 Column3
------- ------- -------
jim       1788    5F 
jim       2000    9F
jim       500     9F
ben       190     4H
matt      400     46
matt      20      3G

I need to run a query that outputs:

Column1 MaxValue PL
------- ------- -------
jim       2000    9F
jim       2000    NULL
ben       190     4H
matt      400     46
matt      400     NULL

For each value in Column1 (e.g. jim, ben, matt): we group the data by Column1 and for each group we display the row that has the maximum value on column2. Then, for each row found in this manner, it displays it again but with NULL in column3, if the groupping by Column1 returns more than 1 row and there are smaller values in Column2 than the maximum found number in the previous step. ben 190 NULL is not displayed because we have ben only once on Column1.

Thank you in advance for any tips or suggestions.

This is what I tried so far but I receive an error prompting me to include Column2 and Column3 in the GROUP By clause, but if I do so I don't reach the desired output as shown above.

CREATE VIEW VIEWB AS 
   SELECT DISTINCT t1.Column1, 
          /* MAX_Value */
            (MAX(t1.[Column2])) AS [MAX Value], 
          /* PL */
            (CASE 
            WHEN t1.[Column2] = MAX(t1.[Column2]) THEN t1.[Column3]
            ELSE NULL
            END) AS PL
      FROM TABLEA AS t1
      GROUP BY t1.Column1;

Upvotes: 2

Views: 216

Answers (5)

Ivan Golović
Ivan Golović

Reputation: 8832

Try this code:

DECLARE @t TABLE (Column1 VARCHAR(50), Column2 INT, Column3 VARCHAR(50))
INSERT  @t
VALUES  
('jim'       ,1788    ,'5F'),
('jim'       ,2000    ,'9F'),
('jim'       ,500     ,'9F'),
('ben'       ,190     ,'4H'),
('matt'      ,400     ,'46'),
('matt'      ,20      ,'3G')

;WITH a AS (
SELECT  *,
        ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2 DESC) RowNum,
        MAX(Column2) OVER (PARTITION BY Column1) Maximum
FROM    @t
)

SELECT  Column1,
        Maximum,
        CASE WHEN RowNum = 1 THEN Column3 END
FROM    a
WHERE   RowNum IN (1, 2)
ORDER   BY Column3 DESC

If you need to, you can put this in a view.

Upvotes: 2

sfnhltb
sfnhltb

Reputation: 21

Might not be the most efficient way of doing this, so if you have a really large table this may not be an ideal solution, but an option:

create table #temp (column1 varchar(10), column2 float, column3 varchar(10))

insert #temp select 'jim', 1788, '5F'
insert #temp select 'jim', 2000, '9F'
insert #temp select 'jim', 500, '9F'
insert #temp select 'ben', 190, '4H'
insert #temp select 'matt', 400, '46'
insert #temp select 'matt', 20, '3G'

SELECT column1, column2 as MaxValue, column3 as PL FROM #temp 
WHERE column2=(SELECT Max(column2) FROM #temp t2 WHERE t2.column1=#temp.column1)
union
SELECT column1, column2, NULL FROM #temp
WHERE column2=(SELECT Max(column2) FROM #temp t3 WHERE t3.column1=#temp.column1)
AND EXISTS(SELECT 1 FROM #temp t4 WHERE t4.column2<>#temp.column2 and t4.column1=#temp.column1)

DROP TABLE #temp

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

declare @t table(column1 varchar(10),column2 int, column3 varchar(10))
insert into @t 
select 'jim',       1788,    '5F' union all  
select 'jim',       2000,    '9F' union all 
select 'jim',       500,     '9F' union all
select 'ben',       190,     '4H' union all
select 'matt',      400,     '46' union all
select 'matt',      20,      '3G'


select column1,column2,column3 from
(
select *, row_number() over (partition by column1 order by column2 desc) as sno from @t
) as t
where sno=1
union 
select t1.column1,t2.column2,NULL 
from @t  as t1 inner join
(
select Column1, max(Column2) as column2,count(*) as counting from @t 
group by column1 having count(*)>1
) as t2
on t1.column1=t2.column1

Upvotes: 1

GSerg
GSerg

Reputation: 78155

with data as (
  select * from
  (values
    ( 'jim'   ,    1788  ,  '5F' ),
    ( 'jim'   ,    2000  ,  '9F' ),
    ( 'jim'   ,    500   ,  '9F' ),
    ( 'ben'   ,    190   ,  '4H' ),
    ( 'matt'  ,    400   ,  '46' ),
    ( 'matt'  ,    20    ,  '3G' )
  ) foo (col1, col2, col3)
),
maxes as (
  select d.col1, d.col2, d.col3
  from
    data d
    inner join (select col1, max(col2) as col2 from data group by col1) m on d.col1 = m.col1 and d.col2 = m.col2 
)
select col1, col2, col3
from maxes
union all
select col1, col2, null
from maxes
where exists (select 0 from data where data.col1 = maxes.col1 and data.col2 < maxes.col2)
order by col1, col3 desc

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this

;with cte as (select *,ROW_NUMBER() over(partition by Column1 
order by Column2 desc) as row_num from table_A),
a as (
select Column1,MAX(Column2) [Column2],null [row_num]
from table_A
group by Column1
having COUNT(*)>1),
b as (select Column1,Column2,Column3 from cte where row_num=1
union all
select * from a)
select  Column1,Column2 [MaxValue],Column3 [PL] from b
order by Column2 desc,Column1,ISNULL(Column3,'') desc

Upvotes: 1

Related Questions