Complex Query Question

[clifgriffin]

I need some SQL advice...

I'm writing an application that will track passwords for the
organization I work with. It uses a field HistoryID as an ID for the
password. And generation, as a way to track old passwords. Each time a
password is updated, the generation is incremented by 1. The HistoryID
with the highest Generation is the current password for a given
generation.

The table looks like so...

Username Password HistoryID Generation
clifton Something 12 0
clifton Something1 12 1
steven Yours 10 8
steven Yours2 10 9

I want to do a query that pulls all historyIDs, but only the ones with
the highest generation... So the query would return...

Username Password HistoryID Generation
clifton Something1 12 1
steven Yours2 10 9

How do I do this? I can't think through the logic in order to write
the query or subquery... I can get the record with the highest
generation for a given historyID, but not a set of all of them.

Any ideas?

 

[Guest]

Try something like

Select T1.* From TableName As T1 Where
T1.Generation In (Select Top 1 T2.Generation From TAbleNAme As T2 Where
T2.HistoryID = T1.HistoryID Order By T2.Generation Desc)

Look for furthor examples sub queries in this link

http://allenbrowne.com/subquery-01.html

 

[John Spencer]

You should be able to use a correlated subquery to do this.

SELECT UserName, HistoryID, Password, Generation
FROM TheTable
WHERE Generation =
(SELECT Max(Generation)
FROM TheTable as Tmp
WHERE Tmp.UserName = TheTable.UserName
AND Tmp.HistoryID = TheTable.HistoryID)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..