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
No, it is not.
But I would try something like this:
---- Andy
There is a great need for a sarcasm font.
RE: Seek Max record of a Group
Here is what I ended up doing. It isn't elegant and took 2 queries and couple of temp tables but it worked:
CODE -->
Jim
RE: Seek Max record of a Group
---- Andy
There is a great need for a sarcasm font.
RE: Seek Max record of a Group
If you want more of the fields, repeat the LAST_VALUE structure for each one you want.
Tamar