get latest date record with different unique ids

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get latest date record with different unique ids

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2011-01-07 : 16:24:28

I have a complex query where im getting
id, lastname, firstname, buid, course, date and status

I want to get the latest date (based on lastname, firstname, buid and course) but the problem is ID will always be unique and I need that.

so for example:


144034	Abraham		Tom		12345689	Laboratory Safety C Training		10/29/2010 0:00	1
154290	Abraham		Tom		12345689	Laboratory Safety Annual Training	8/19/2010 0:00	1
154291	Abraham		Tom		12345689	Laboratory Safety Annual Training	5/27/2009 0:00	1
155999	Abraham		Tom		12345689	Basic Radiation Safety			3/30/2010 0:00	1

would show me:


144034	Abraham		Tom		12345689	Laboratory Safety C Training		10/29/2010 0:00	1
154290	Abraham		Tom		12345689	Laboratory Safety Annual Training	8/19/2010 0:00	1
155999	Abraham		Tom		12345689	Basic Radiation Safety			3/30/2010 0:00	1

here is the sql


SELECT p1.id, p3.textdata as lastname, p4.textdata as firstname, p5.textdata as buid,
p2.textdata as course, p7.datedata as date, CASE encode(p8.binarydata,'hex') WHEN '80' THEN 1 ELSE 0 END AS status
FROM ifield p1
	JOIN ifield p2 ON p1.referencediform = p2.iform AND p2.lfield = 1471 -- Course Name
	JOIN ifield p3 ON p1.iform = p3.iform AND p3.lfield = 1439 -- Last Name
	JOIN ifield p4 ON p1.iform = p4.iform AND p4.lfield = 2137 -- First Name
	JOIN ifield p5 ON p1.iform = p5.iform AND p5.lfield = 1440 -- BUID
	JOIN ifield p6 ON p1.iform = p6.iform AND p6.lfield = 1447 -- reference for date
	JOIN ifield p7 ON p6.referencediform = p7.iform AND p7.lfield = 1588 -- date
	LEFT JOIN ifield p8 ON p1.iform = p8.iform AND p8.lfield = 4744 -- Status
WHERE p1.iform IN (SELECT iformid FROM iform WHERE vform = 48) AND p1.lfield = 1442 -- reference for course name

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 16:39:21

there are a couple of ways, one is to use a ranking function like ROW_NUMBER(). for example:

SELECT *
FROM
(
SELECT p1.id, p3.textdata as lastname, p4.textdata as firstname, p5.textdata as buid,
p2.textdata as course, p7.datedata as date, CASE encode(p8.binarydata,'hex') WHEN '80' THEN 1 ELSE 0 END AS status,
ROW_NUMBER() OVER(PARTITION BY p3.textdata, p4.textdata, p5.textdata, p2.textdata ORDER BY p7.datedata DESC) AS RowNum  
FROM ifield p1
	JOIN ifield p2 ON p1.referencediform = p2.iform AND p2.lfield = 1471 -- Course Name
	JOIN ifield p3 ON p1.iform = p3.iform AND p3.lfield = 1439 -- Last Name
	JOIN ifield p4 ON p1.iform = p4.iform AND p4.lfield = 2137 -- First Name
	JOIN ifield p5 ON p1.iform = p5.iform AND p5.lfield = 1440 -- BUID
	JOIN ifield p6 ON p1.iform = p6.iform AND p6.lfield = 1447 -- reference for date
	JOIN ifield p7 ON p6.referencediform = p7.iform AND p7.lfield = 1588 -- date
	LEFT JOIN ifield p8 ON p1.iform = p8.iform AND p8.lfield = 4744 -- Status
WHERE p1.iform IN (SELECT iformid FROM iform WHERE vform = 48) AND p1.lfield = 1442
) AS T
WHERE RowNum = 1

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2011-01-07 : 18:54:19

Wow thanks!! looks to be working but ill check it out on monday :)

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-01-08 : 22:34:51

quote:

Originally posted by Lamprey

there are a couple of ways, one is to use a ranking function like ROW_NUMBER(). for example:

SELECT *
FROM
(
SELECT p1.id, p3.textdata as lastname, p4.textdata as firstname, p5.textdata as buid,
p2.textdata as course, p7.datedata as date, CASE encode(p8.binarydata,'hex') WHEN '80' THEN 1 ELSE 0 END AS status,
ROW_NUMBER() OVER(PARTITION BY p3.textdata, p4.textdata, p5.textdata, p2.textdata ORDER BY p7.datedata DESC) AS RowNum  
FROM ifield p1
	JOIN ifield p2 ON p1.referencediform = p2.iform AND p2.lfield = 1471 -- Course Name
	JOIN ifield p3 ON p1.iform = p3.iform AND p3.lfield = 1439 -- Last Name
	JOIN ifield p4 ON p1.iform = p4.iform AND p4.lfield = 2137 -- First Name
	JOIN ifield p5 ON p1.iform = p5.iform AND p5.lfield = 1440 -- BUID
	JOIN ifield p6 ON p1.iform = p6.iform AND p6.lfield = 1447 -- reference for date
	JOIN ifield p7 ON p6.referencediform = p7.iform AND p7.lfield = 1588 -- date
	LEFT JOIN ifield p8 ON p1.iform = p8.iform AND p8.lfield = 4744 -- Status
WHERE p1.iform IN (SELECT iformid FROM iform WHERE vform = 48) AND p1.lfield = 1442
) AS T
WHERE RowNum = 1

LamPrey,
I checked your code out and I found a few errors, Could you calrify this out for me
please.

Msg 195, Level 15, State 10, Line 8
'encode' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AND'.

What is the appropriate function in MS SQL for encode

Happy new Year and thanks
Glen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 01:19:45

Is encode a UDF you've created? if yes, call it like <schema>.encode i.e dbo.encode if your default schema is dbo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources