max of multiple columns - 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

max of multiple columns

Author

Topic

david_reinjal
Starting Member

36 Posts

Posted - 2007-07-16 : 07:19:08

hi guys,

I have a table which has 4 columns A,B,C,D. it is somewhat like this:

A B C

S U 8
S U 10
S U 3
s V 14
S V 36
T U 25
T U 34
T U 9
T V 5
T V 1

This is the way i have in my database. I need to check which is the max in column C for values in Column A and Column B. First i need to start from column A. If it is S, then i need to see column B, if it is U then max[Column C] for only U & S. then when it becomes V, I need to get max [Column C] for only V & S. So on and so forth. So finally my output should be:

S U 10
S V 36
T U 34
T V 5

How can i do this?

regards,
David

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-16 : 07:41:45

Select columns from table T where colc=(select max(colc) as colc from table where cola=T.cola and colb=T.colb)

Madhivanan

Failing to plan is Planning to fail

david_reinjal
Starting Member

36 Posts

Posted - 2007-07-16 : 08:03:04

thanks dude..i will just try it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:31:58

Based on your original sample data, this will do



SELECT    A,
          B,
          MAX(C)
FROM      Table1
GROUP BY  A,
          B
ORDER BY  A,
          B

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:48:47

[code]-- Create sample data
DECLARE @Sample TABLE (A CHAR(1), B CHAR(1), C TINYINT)

INSERT @Sample (A, B, C)
SELECT 'S', 'U', 8 UNION ALL
SELECT 'S', 'U', 10 UNION ALL
SELECT 'S', 'U', 3 UNION ALL
SELECT 's', 'V', 14 UNION ALL
SELECT 'S', 'V', 36 UNION ALL
SELECT 'T', 'U', 25 UNION ALL
SELECT 'T', 'U', 34 UNION ALL
SELECT 'T', 'U', 9 UNION ALL
SELECT 'T', 'V', 5 UNION ALL
SELECT 'T', 'V', 1

-- Madhivanan
SELECT t.A,
t.B,
t.C
FROM @Sample AS t
WHERE t.c = (SELECT MAX(x.C) AS c FROM @Sample AS x WHERE x.A = t.A AND x.B = t.B)
ORDER BY t.A,
t.B

-- Peso 1
SELECT A,
B,
MAX(C) AS c
FROM @Sample
GROUP BY A,
B
ORDER BY A,
B

-- Peso 2
SELECT d.A,
d.B,
d.C
FROM (
SELECT A,
B,
C,
ROW_NUMBER() OVER (PARTITION BY A, B ORDER BY C DESC) AS RecID
FROM @Sample
) AS d
WHERE d.RecID = 1
ORDER BY d.A,
d.B[/code]

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-16 : 11:49:46

quote:
Originally posted by madhivanan

Select columns from table T where colc=(select max(colc) as colc from table where cola=T.cola and colb=T.colb)

Madhivanan

Failing to plan is Planning to fail

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:51:29

It is an attempt to imitate SQL Server 2005 ROW_NUMBER() functionality in SQL Server 2000

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-17 : 00:10:28

quote:
Originally posted by Peso

It is an attempt to imitate SQL Server 2005 ROW_NUMBER() functionality in SQL Server 2000

Peter Larsson
Helsingborg, Sweden

Yes it is

Madhivanan

Failing to plan is Planning to fail

david_reinjal
Starting Member

36 Posts

Posted - 2007-07-17 : 00:18:58

thanks peso. it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources