return latest data - 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

Transact-SQL (2000)

return latest data

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-12-02 : 23:15:09

Hi Friends
I've following fields in a table.in some records all fields will be same except transdate field (which contains date when this particular record is created)

claim,transdate,ctype,srvprovno

how can i write a sql which returns latest record(if there r multiples)

sample data
claim,transdate,ctype,srvprovno
001,12/11/04,h,1233H
001,03/12/04,h,1233H
002,02/12/04,T,1111H
003,12/11/04,S,0111H
003,13/11/04,S,0111H

for the above data i want following result
001,03/12/04,h,1233H
002,02/12/04,T,1111H
003,13/11/04,S,0111H

Please suggest sql for this.I tried ading group by but that did not return the data i wanted.
Thanks

Cheers

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-03 : 00:58:10

May be this query will work for u

select claim, max(transdate), ctype, srvprovno from table
group by claim, ctype, srvpronvo

HTH

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-12-04 : 15:56:29

Hippi
That's great.it worked beautifully for me :-)

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources