Getting last record depending on field value

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 2000 Forums

Transact-SQL (2000)

Getting last record depending on field value

Author

Topic

domo
Starting Member

23 Posts

Posted - 2008-04-10 : 08:12:37

Here's a data sample:

mrID, mrSEQUENCE, mrFIELDNAME, mrNEWFIELDVALUE, mrOLDFIELDVALUE, mrUSERID
1867, 3244, mrSTATUS, Request, NULL, DH4378
1867, 3245, mrSTATUS, Open, Request, HD9034
1867, 3705, mrSTATUS, Closed, Open, DH4378
1867, 3246, mrSTATUS, Reopened, Closed, DH4378
1868, 3249, mrSTATUS, Request, NULL, DL4848
1868, 3250, mrSTATUS, Open, Request, DL4848
1870, 3255, mrSTATUS, Closed, Open, DL4848

I need to output all instances where mrNEWFIELDNAME = 'Closed' AND its the last in mrSEQUENCE.
mrID 1867 fails to meet the criteria as the last new value added is 'ReOpened'. 1870 Qualifies as the last new value added is 'Closed'.

The query should return the the output as if viewing the table, only showing one instance of mrID.

The results if run against the sample above would be:
1870, 3255, mrSTATUS, Closed, Open, DL4848

Your help would be happily received

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 08:18:03

What defines the order of records in the table?

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 08:32:58

To me both 1867 and 1870 qualify as desired records since both of your criterias are satisfied. If not, then your mrSEQUENCE is out of sequence.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-10 : 13:07:53

[code]SELECT t1.mrID, t1.mrSEQUENCE, t1.mrFIELDNAME, t1.mrNEWFIELDVALUE, t1.mrOLDFIELDVALUE, t1.mrUSERID
FROM YourTable t1
INNER JOIN (SELECT mrID,MAX(mrSEQUENCE) AS MaxSequence
FROM YourTable
GROUP BY mrID)t2
ON t2.mrID=t1.mrID
AND t2.MaxSequence=t1.mrSEQUENCE
WHERE t1.mrNEWFIELDVALUE='Closed'[/code]

EDIT:Added aliases in SELECT list

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 14:45:26

Visakh, if you read OP carefully, you can see that the mrSEQUENCE do not apply for "last" record.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources