MAX with Grouping? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

MAX with Grouping?

Author

Topic

cipher
Starting Member

10 Posts

Posted - 2007-07-14 : 21:04:45

We have a table with data similar to the following:

CREATE TABLE TEST(
ID int identity,
CATEGORY varchar(30),
VERSION int)

INSERT INTO TEST VALUES('A', 1)
INSERT INTO TEST VALUES('A', 2)
INSERT INTO TEST VALUES('C', 1)
INSERT INTO TEST VALUES('C', 2)
INSERT INTO TEST VALUES('D', 1)
INSERT INTO TEST VALUES('D', 2)
INSERT INTO TEST VALUES('D', 3)
INSERT INTO TEST VALUES('E', 4)

What I'm trying to return is the latest VERSION for each CATEGORY. As well, we need all fields, including ID, to be returned. Therefore, the output we're looking for is...

ID CATEGORY VERSION
----------- ------------------------------ -----------
2 A 2
4 C 2
7 D 3
8 E 4

I've tried the MAX statement on the Version column, grouped by CATEGORY, but haven't figured out how to also bring back the ID field.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-14 : 21:33:14

[code]SELECT t.*
FROM TEST t
INNER JOIN
(
SELECT CATEGORY, VERSION = MAX(VERSION)
FROM TEST
GROUP BY CATEGORY
) m
ON t.CATEGORY = m.CATEGORY
AND t.VERSION = m.VERSION
ORDER BY t.ID[/code]

KH
[spoiler]Time is always against us[/spoiler]

cipher
Starting Member

10 Posts

Posted - 2007-07-14 : 23:51:40

Perfect!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-15 : 04:51:55

And the SQL Server 2005 specific alternative:

SELECT ID, Category, Version
FROM (SELECT ID, Category, Version, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Version) AS RecID) AS d
WHERE RecID = 1
ORDER BY RecID

Peter Larsson
Helsingborg, Sweden

coreyfurman
Starting Member

2 Posts

Posted - 2007-07-20 : 11:49:37

I'm doing something just like this, and I'm sure somehow this will work for me, once I figure out the syntax.

Thanks.

coreyfurman
Starting Member

2 Posts

Posted - 2007-07-20 : 14:00:09

Got this working, thanks.

ekb18c
Starting Member

18 Posts

Posted - 2007-07-22 : 08:34:37

Just curious on why you wouldn't want to do this:

SELECT
MAX(ID),
CATEGORY,
MAX(VERSION)
FROM
TEST
GROUP BY
CATEGORY

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 08:57:15

Because there is no gurantee that version will inserted sequentially?

Peter Larsson
Helsingborg, Sweden

ekb18c
Starting Member

18 Posts

Posted - 2007-07-22 : 11:35:50

If version wasn't inserted sequentially then

SELECT t.*
FROM TEST t
INNER JOIN
(
SELECT CATEGORY, VERSION = MAX(VERSION)
FROM TEST
GROUP BY CATEGORY
) m
ON t.CATEGORY = m.CATEGORY
AND t.VERSION = m.VERSION
ORDER BY t.ID

Would fail too, no?

I also tried your code peso and it's not working, something I must be over looking.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 14:02:17

Yes. Exactly as khtan posted earlier.

Peter Larsson
Helsingborg, Sweden

somu_p
Starting Member

13 Posts

Posted - 2007-07-23 : 02:55:10

SELECT ID,Category,Version FROM TEST
WHERE Version In(SELECT Max(version) FROM TEST GROUP BY Category)

Regards,
Somu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources