×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Seek Max record of a Group

thread183-1803962

Seek Max record of a Group

Seek Max record of a Group

(OP)


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

RE: Seek Max record of a Group

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

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

But I would try something like this:

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

---- Andy

There is a great need for a sarcasm font.

RE: Seek Max record of a Group

(OP)
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

RE: Seek Max record of a Group

ParticipantID, NameEntryID - that's new... ponder


---- Andy

There is a great need for a sarcasm font.

RE: Seek Max record of a Group

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

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
...

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

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close