Query to Get Latest Record For Each ID

[EnginerdUNH]

Hi,

I have a table which I am trying to write a query to get the latest record for each unique ID value. For simplicity of this thread, I am only going to include the two columns in this post which I think need to be included as part of the query. Let's say the two columns in the table look like the following:

SampleID    NoOfRoutingChanges
    S001                1
    S001                2
    S001                3
    S002                1
    S002                2
    S003                1
    S001                4
    S002                3

With the above data, as you can see the SampleID will repeat but the NoOfRoutingChanges column will never repeat for the same SampleID. The query should return the rows with NoOfRoutingChanges equal to 4 for S001, 3 for S002 and 1 for S003. First, I tried to accomplish this using the following query but it every record:

Code: Select all

SELECT tblMain.SampleID, Max(tblSampleMain.SampleNoOfRoutingChanges) AS MaxOfSampleNoOfRoutingChanges
FROM tblSampleMain
GROUP BY tblSampleMain.SampleID

Next, I tried to modify the query as follows but it returned a no results:

Code: Select all

SELECT tblMain.SampleID, Max(tblSampleMain.SampleNoOfRoutingChanges) AS MaxOfSampleNoOfRoutingChanges
FROM tblSampleMain
GROUP BY tblSampleMain.SampleID
HAVING (((Max(tblSampleMain.SampleNoOfRoutingChanges))>Nz((SELECT Max(tblSampleMain.VIRNoOfRoutingChanges) FROM tblSampleMain AS Q WHERE Q.SampleID = [tblSampleMain].[SampleID]),0)));

Can someone please tell me what I'm doing wrong?

[robertocm]

All credits for Bill Karwin
more references looking for: greatest-n-per-group

Here an example with SQL Server, but i think the idea of the SELF JOIN could be adapted to Access SQL

Code: Select all

;WITH [cte]([c1],[c2],[c3],[c4]) AS
(
    SELECT *
    FROM (
        VALUES
        (1, 'Adrian', 25,  CONVERT(DATETIME, '01/08/2020', 103)),
        (2, 'Juan',   23,  CONVERT(DATETIME, '01/08/2020', 103)),
        (3, 'Adrian', 22,  CONVERT(DATETIME, '20/08/2020', 103)),
        (4, 'Pedro',  24,  CONVERT(DATETIME, '01/08/2020', 103)),
        (5, 'Agus', 20,  CONVERT(DATETIME, '01/08/2020', 103))
    )
    AS [cte]([c1],[c2],[c3],[c4])
)
SELECT subset1.*
FROM cte AS subset1
LEFT OUTER JOIN cte AS subset2
  ON subset1.c2 = subset2.c2 AND subset1.c4 < subset2.c4
WHERE subset2.c2 IS NULL
ORDER BY subset1.c2;

Returns:

Code: Select all

3  Adrian  22  10/08/2020
5  Agus    20  01/08/2020
2  Juan    23  01/08/2020
4  Pedro   24  01/08/2020

Notes:

notes_greatest-n-per-group.xlsx

[HansV]

Use a subquery:

Code: Select all

SELECT * 
FROM tblMain 
WHERE NoOfRoutingChanges = 
   (SELECT Max(T.NoOfRoutingChanges) 
    FROM tblMain AS T
    WHERE T.SampleID = tblMain.SampleID
    GROUP BY T.SampleID)

[EnginerdUNH]

Use a subquery:

Code: Select all

SELECT * 
FROM tblMain 
WHERE NoOfRoutingChanges = 
   (SELECT Max(T.NoOfRoutingChanges) 
    FROM tblMain AS T
    WHERE T.SampleID = tblMain.SampleID
    GROUP BY T.SampleID)

Amazing! that did what I needed!