select max for group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select max for group by

Author

Topic

dainova
Starting Member

31 Posts

Posted - 2012-08-07 : 03:08:08

Hi all
I'm looking (and learning-)) how to achieve in SQLServer the following task:

I need to select max(Amount) for entries grouped by (Term+Param1+Param2) so I expect to get
300.1.0.120
300.1.1.520
300.1.3.620 for this table below


Term  Param1 Param2 Param3 Param4  Amount
300   1      0      3      1       120
300   1      0      3      1       075
300   1      1      4      2       340
300   1      1      5      3       520
300   1      3      2      4       620

Is there any kind of analytical func in Sqlserver ?
Tx
Dai

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-08-07 : 03:25:13

This will do...

CREATE TABLE TEST(TERM INT, Param1 INT, Param2 INT, Param3 INT, Param4 INT, Amount BIGINT)
INSERT INTO TEST
SELECT 300,1,0,3,1,120 UNION ALL
SELECT 300,1,0,3,1,075 UNION ALL
SELECT 300,1,1,4,2,340 UNION ALL
SELECT 300,1,1,5,3,520 UNION ALL
SELECT 300,1,3,2,4,620

SELECT TERM, Param1, Param2, MAX(Amount) Amount
FROM TEST
GROUP BY TERM, Param1, Param2

------------------------------------------------
The answer is always no till than you don't ask.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 09:53:55

[code]
SELECT TERM,Param1,Param2,Amount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY TERM,Param1,Param2 ORDER BY Amount DESC) AS Seq,*
FROM table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources