1

Lets say I have a table that looks like the following;

version_number    ID    Value
1                 33    Foo
2                 33    Bar
1                 37    Foo

How would I return the Value field from the table if the ID was 33 and only select from the Maximum version number? (In this case, 2)

Something like;

SELECT Value FROM Table WHERE ID = 33 and MAX(version_number)

EDIT: Both answers below work. I'm going with Tim Schmelter's answer just because I think its a little more elegant, but I've up voted them both. Thanks guys :)

Ryan Hargreaves
  • 267
  • 3
  • 16

2 Answers2

3

In sql-server you can use ranking functions like ROW_NUMBER, f.e. with a CTE:

WITH CTE AS
(
   SELECT t.*, rn = Row_Number() Over (Partition By ID  Order By version_number Desc)
   FROM dbo.TableName t
)
Select Value From CTE
WHERE  ID = 33 
   AND rn = 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • that works if I want to specify the version_number, but I need it so it conforms to whatever the MAX is for the specified value. So for example, searching for ID 33 gets me the row with version_number 2, but searching for ID 37 gets me the row with version_number 1, if that makes sense? – Ryan Hargreaves Aug 02 '16 at 13:30
  • @RyanHargreaves: it's working in that way. It will always return the row which belongs to the max-version number per ID (because of the `Partition By ID`). If you want to get the minimum version change `version_number Desc` to `version_number Asc`. – Tim Schmelter Aug 02 '16 at 13:31
  • Sorry, your are correct, I forgot to order mine Desc. Thanks :) – Ryan Hargreaves Aug 02 '16 at 13:33
1

You can use a subquery for this:

SELECT Value FROM
( SELECT TOP 1 *
  FROM Table
  WHERE ID = 33 
  ORDER BY version_number
  ) as t

That is, start by getting the filtered data and then just print the desired column.

fedorqui
  • 275,237
  • 103
  • 548
  • 598