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.
| Author |
Topic |
|
glt101
Starting Member
28 Posts |
 Posted - 2010-08-12 : 18:11:44
|
| Hi All,I have run into this a couple of times and never found a simple solution.In a table with three columnsID int (always unique)SN char(7)Version floatThere may be many entries of a given SN with differing values ofVersion. There may also be many different values of SN.One set of rows for a given SN might look like:ID, SN, Version100, 987687, 1.0150, 987687, 1.1204, 987687, 2.01032, 987687, 3.5How do I return a results set that lists only SNs that have themaximum value of Version for that particular SN? I tried usinga GROUP BY clause and adding HAVING Version = MAX(Version)but unsurprisingly that doesn't work.Sorry if this is an FAQ.Cheers,Geoff |
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-12 : 18:33:15
|
| Have you tried SELECT MAX(Version) instead? |
 |
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-08-12 : 18:41:02
|
| [code]SELECT *FROM (SELECT *, rec_id = ROW_NUMBER() OVER(PARTITION BY SN ORDER BY Version DESC) FROM table_name) DWHERE rec_id = 1;[/code] |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-12 : 19:09:17
|
| You'll need to use OVERSELECT *FROM (SELECT *, rec_id = ROW_NUMBER() OVER (PARTITION BY SN ORDER BY VERSION DESC) FROM @test) DWHERE rec_id = 1 |
|
|
|
glt101
Starting Member
28 Posts |
|
|
|
|
|
|
|
HAVING clause befuddlement