SQL Query Help - SQL Server Forums

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

SQL Server Development (2000)

SQL Query Help

Author

Topic

bidhu
Starting Member

3 Posts

Posted - 2008-08-11 : 14:23:50

I have a situation like this. I have a table containing following records :

POLICY_ID...........TRS_SEQUENCE_NBR..............LAST_MDF_ACY_TS
CHAR(16).....................INTEGER.....................TIMESTMP
----------------....----------------...--------------------------
03A9LGWTDB2SPREM...................1...2007-10-05-14.09.59.368193
03A9LGWTDB2SPREM...................2...2007-11-07-11.09.59.012354
MZ61CUAWZ34SPREM...................1...2007-10-05-14.09.59.368193
MZ61CUAWZ34SPREM...................2...2007-11-06-12.09.59.368193
MZ61CUAWZ34SPREM...................3...2007-12-07-15.09.59.368193

I want to extract all POLICY_IDs which have LAST_MDF_ACY_TS time stamp >= 2007-11-07-11.09.59.012354 and MAX TRS_SEQUENCE_NBR. I expect 2 rows to be returned. The 2nd and the 5th. Please help.

Please note the dots are spaces as this editor is not allowing to have spaces.

Thanks
Bidhu

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-08-11 : 14:36:46

Welcome to SQLTeam, it is good to have you here as a member.
This should be what you are looking for.


SELECT Policy_ID, Max(TRS_SEQUENCE_NBR)
FROM YOURTABLE
WHERE LAST_MDF_ACY_TS >='2007-11-07-11.09.59.012354'
GROUP BY Policy_ID

Dane

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 14:37:01

[code]SELECT t.POLICY_ID,t.TRS_SEQUENCE_NBR,t.LAST_MDF_ACY_TS
FROM YourTable t
INNER JOIN (SELECT POLICY_ID,MAX(TRS_SEQUENCE_NBR) AS MaxRec
FROM YourTable
GROUP BY POLICY_ID) t1
ON t1.MaxRec=t.TRS_SEQUENCE_NBR
AND t1.POLICY_ID=t.POLICY_ID
WHERE t.LAST_MDF_ACY_TS>='2007-11-07-11.09.59.012354'[/code]

bidhu
Starting Member

3 Posts

Posted - 2008-08-11 : 15:06:52

Thanks Dane its working. Suppose I have few more columns and I want to extract them as well in this criteria, what will be the query.

bidhu
Starting Member

3 Posts

Posted - 2008-08-11 : 15:25:19

Vikash, I tried your way and it worked for me answering the 2nd query. Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources