Seek Max record of a Group

jimoo · Jul 13, 2020

I am seeking a query that will return the last item (max) based on a set. The personID is the same for several records. I would like the last record for each.
For the 1st person (1458748) the nameid I'd like to return is 1405 and the name lastname is Smith.

In short, there are 6 sets in this example and I'd like that last one either by the personid/nameid field or personid/modifiedon field, both should produce same result based on this data.

I marked the expected value with * at the end of the group.

Any suggestions for retrieving this data?

Code:

NameID  lastName        middleName      firstName       PersonID        modifiedOn
1404	SAMPLE          SUPER           SAMMY           1458738         6/26/15 3:01 PM
1405	SMITH           SUPER           SAMMY           1458738         6/26/15 3:03 PM  *
1406	TEST            TERRIBLE	TERRY           1458740         6/26/15 3:06 PM
1407	TEST            TERRIBLE	TERRY           1458740         6/26/15 3:08 PM *
1409	SAMPLE          SUPER           SAMMY           1458748         6/26/15 3:02 PM
1410	SAMPLE          SUPER           SAMMY           1458748         6/26/15 3:03 PM  *
1411	TEST            TERRIBLE	TERRY           1458749         6/26/15 2:58 PM
1413	TEST            TERRIBLE	TERRY           1458749         6/26/15 2:59 PM *
1779	PELL                            SAM             1467824         8/6/15 3:01 PM
1780	JOYGOOD                         SAM             1467824         8/6/15 3:03 PM
1783	JOYGOOD                         SAM             1467824         8/6/15 3:04 PM
1784	JOYGOOD                         SAM             1467824         8/6/15 3:08 PM *
4634	DOE             BOB             JOHN            1775741         6/1/16 3:15 PM
4635	DOE             BOB             JOHN            1775741         6/1/16 3:14 PM
4857	DOE             BOB             JOHN            1775741         4/12/16 10:41 AM
4858	HONEY           BOB             JOHN            1775741         4/12/16 10:43 AM *

Jim

Andrzejek · Jul 14, 2020

>For the 1st person ([blue]1458748[/blue]) the nameid I'd like to return is 1405 and the name lastname is Smith.
No, it is not.

[pre]
NameID lastName middleName firstName PersonID modifiedOn
...
1409 SAMPLE SUPER SAMMY [blue]1458748[/blue] 6/26/15 3:02 PM
1410 SAMPLE SUPER SAMMY [blue]1458748[/blue] 6/26/15 3:03 PM *
...
[/pre]

But I would try something like this:

[pre]
Select NameID, PersonID, modifiedOn
From MyTable,
(Select PersonID, MAX(modifiedOn) As MyMax
From MyTable Group By PersonID) X
Where MyTable.PersonID = X.PersonID
And MyTable.modifiedOn = X.MyMax
[/pre]

---- Andy

There is a great need for a sarcasm font.

jimoo · Jul 14, 2020

Thanks Andy, I'll give that a try.

Here is what I ended up doing. It isn't elegant and took 2 queries and couple of temp tables but it worked:

Code:

SELECT ParticipantID,MAX(NameEntryID) as PNameEntryID
INTO #temp1
FROM NameEntry 
GROUP BY ParticipantID;

select p.ParticipantID, p.NameEntryID, lastname, firstname, middleName
INTO #temp2
from NameEntry p 
join #temp1 t on p.ParticipantID = t.ParticipantID and p.NameEntryID = t.NameEntryID
order by 1, 2

Jim

Andrzejek · Jul 14, 2020

[tt]ParticipantID, NameEntryID[/tt] - that's new... [ponder]

---- Andy

There is a great need for a sarcasm font.

TamarGranor · Jul 14, 2020

If you're in SQL Server 2012 or later, you can use the LAST_VALUE() function with OVER to get this:

[pre]
SELECT PersonID,
MAX(modifiedOn) OVER (PARTITION BY PersonID) AS modifiedOn,
LAST_VALUE(NameID) OVER (PARTITION BY PersonID ORDER BY modifiedOn
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS NameID
...
[/pre]

If you want more of the fields, repeat the LAST_VALUE structure for each one you want.

Tamar

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Seek Max record of a Group

jimoo

Programmer

Andrzejek

Programmer

jimoo

Programmer

Andrzejek

Programmer

TamarGranor

Programmer

Similar threads

Part and Inventory Search

Sponsor