Select: most recent version value 1

[glyph1]

Using MSDE MS SQL Server 2000

I have a table named Visitor that has two primary keys (int), Visitor_ID and VisitorVersion_ID.
I am trying to return unique Visitor_ID's that have the most recent version, or in other words the Maximum VisitorVersion_ID value.

My current query:

SELECT Distinct Visitor_ID, SSN, FirstName, LastName, MiddleName, MAX(VisitorVersion_ID) AS Version
FROM Visitor
GROUP BY Visitor_ID, SSN, FirstName, LastName, MiddleName
ORDER BY Visitor_ID

Any help would be greatly appreciated. If you need any additional information please ask I will be monitoring this closely. Thank you!

 

[gmmastros]

Try this...

SELECT Visitor.Visitor_ID, SSN, FirstName, LastName, MiddleName, Visitor.VisitorVersion_ID AS Version
FROM   Visitor
       Inner Join (
          Select Visitor_ID, Max(VisitorVersion_ID) As VisitorVersion_ID
          From   Visitor
          Group By Visitor_ID
          ) As A
          On Visitor.Visitor_ID = A.Visitor_ID
          And Visitor.VisitorVersion_ID = A.VisitorVersion_ID

If this returns the right data and you want me to explain how this works, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyph1]

Thank you so much George it works perfectly. If you don't mind I would love to hear the explanation.

 

[gmmastros]

The explanation....

Originally, you had included all the fields in the group by. This causes you problems because if the data is different from row to row (first name, last name, etc...) then that data will return as a separate row. If you try to leave those fields out of the group by, you get a different error message.

Essentially, when using Group By, all of the columns returned MUST be part of an aggregate (min, max, sum, etc...) OR be part of the group by. In your case, both choices are NOT what you want.

To workaround this problem, you can create a derived table.

Step 1, write a query that returns the minimum information you need. In this case, it's Visitor_ID, and VisitorVersion_ID. That query is...

Select Visitor_ID, Max(VisitorVersion_ID) As VisitorVersion_ID
From   Visitor
Group By Visitor_ID

If this was all you needed, you would be done. However, since you want to return the rest of the information in the table that corresponds to the row, you have more work to do. By making this a derived table, and using it to join back to the Visitor table, you can use the principles of the inner join to effectively filter out the records you don't want.

SELECT Visitor.Visitor_ID, SSN, FirstName, LastName, MiddleName, Visitor.VisitorVersion_ID AS Version
FROM   Visitor
       [green]Inner Join ([/green]
          [blue]Select Visitor_ID, Max(VisitorVersion_ID) As VisitorVersion_ID
          From   Visitor
          Group By Visitor_ID[/blue]
          [green]) As A[/green]
          On Visitor.Visitor_ID = A.Visitor_ID
          And Visitor.VisitorVersion_ID = A.VisitorVersion_ID

Then part in [blue]blue[/blue] is the original query that returns the rows you want. The part in [green]green[/green] shows you how to make this a derived table. The ON clause immediately following the green specifies how the Visitor table should link to the derived table.

Does this make sense?

Let me show you an example.

Suppose you have this table.

[tt][blue]
PersonId OrderDate Amount
----------- ------------------ ------------
1 2007-09-01 10.24
1 2007-09-02 15.34
1 2007-09-03 23.14
2 2007-09-04 9.99
[/blue][/tt]

Now, suppose you wanted to get last order by person and what the amount is.

You could try...

Declare @Orders Table(PersonId Int, OrderDate DateTime, Amount Numeric(10,2))

Insert Into @Orders Values(1, '9/1/2007', 10.24)
Insert Into @Orders Values(1, '9/2/2007', 15.34)
Insert Into @Orders Values(1, '9/3/2007', 3.14)
Insert Into @Orders Values(2, '9/4/2007', 9.99)

Select PersonId, Max(OrderDate) As OrderDate, Amount 
From   @Orders
Group By PersonId

This will not work because you included the Amount column but it's not an aggregate, and it's not included in the group by.

You could also try...

Declare @Orders Table(PersonId Int, OrderDate DateTime, Amount Numeric(10,2))

Insert Into @Orders Values(1, '9/1/2007', 10.24)
Insert Into @Orders Values(1, '9/2/2007', 15.34)
Insert Into @Orders Values(1, '9/3/2007', 3.14)
Insert Into @Orders Values(2, '9/4/2007', 9.99)

Select PersonId, Max(OrderDate) As OrderDate, [!]Max([/!]Amount[!])[/!] As Amount
From   @Orders
Group By PersonId

This compiles, but gives you the wrong data.
[tt][blue]
PersonId OrderDate Amount
----------- --------------------------- ------------
1 2007-09-03 00:00:00.000 15.34
2 2007-09-04 00:00:00.000 9.99
[/blue][/tt]

Notice this returns just 1 record per person, but this shows the last amount for person 1 = 15.34, but should really be 3.14. Clearly this doesn't work.

You could also try...

Declare @Orders Table(PersonId Int, OrderDate DateTime, Amount Numeric(10,2))

Insert Into @Orders Values(1, '9/1/2007', 10.24)
Insert Into @Orders Values(1, '9/2/2007', 15.34)
Insert Into @Orders Values(1, '9/3/2007', 3.14)
Insert Into @Orders Values(2, '9/4/2007', 9.99)

Select PersonId, Max(OrderDate) As OrderDate, Amount
From   @Orders
Group By PersonId, Amount

[tt][blue]
PersonId OrderDate Amount
----------- ----------------------------- ---------
1 2007-09-03 00:00:00.000 3.14
2 2007-09-04 00:00:00.000 9.99
1 2007-09-01 00:00:00.000 10.24
1 2007-09-02 00:00:00.000 15.34
[/blue][/tt]

Notice that now all of the records are returned, which is also not what you want.

By using a derived table to get just the rows you want returned, and joining that derived table to the real table, you can control the records AND return as much of the other data as you'd like.

Declare @Orders Table(PersonId Int, OrderDate DateTime, Amount Numeric(10,2))

Insert Into @Orders Values(1, '9/1/2007', 10.24)
Insert Into @Orders Values(1, '9/2/2007', 15.34)
Insert Into @Orders Values(1, '9/3/2007', 3.14)
Insert Into @Orders Values(2, '9/4/2007', 9.99)

Select Orders.PersonId, Orders.OrderDate, Orders.Amount
From   @Orders Orders
       Inner Join (
         Select PersonId, Max(OrderDate) As OrderDate
         From   @Orders
         Group By PersonId
         ) As A
         On Orders.PersonId = A.PersonId
         And Orders.OrderDate = A.OrderDate

[tt][blue]
PersonId OrderDate Amount
----------- ----------------------------- ------------
2 2007-09-04 00:00:00.000 9.99
1 2007-09-03 00:00:00.000 3.14
[/blue][/tt]

Does this help?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyph1]

Thank you again George! This was a great explanation, and it actually tied up a lot of loose ends for me about joins and derived tables. I really appreciate the help, I hope you have a great day!