How to get latest records in all fields

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)

How to get latest records in all fields

Author

Topic

aks
Starting Member

28 Posts

Posted - 2005-06-20 : 13:58:52

Hi,
I'm trying to get max(records) or latest updated records for a paticular client.
Ex fields are:
UID NAME DATE STATUS INTIALS DAYS
78 kijj 05/23/2005 A KJ 23
67 bob 06/18/2005 A b 20
78 kijj 06/20/2005 A KJ
67 bob 06/18/2005 O B 20
67 bob 06/18/2005 23

Now,I want O/P as the latest records of both clients per column like client 67 has
UID NAME DATE STATUS INTIALS DAYS
67 bob 06/20/2005 O B 23
78 kijj 06/20/2005 A Kj 23

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-20 : 14:33:51

Try this:

SELECT t1.UID,t1.NAME, t1.[DATE], t1.STATUS, t1.INITIALS, t1.DAYS from MYTABLE t1 WHERE t1.[DATE] IN (SELECT TOP 1 t2.[DATE] FROM MYTABLE t2 WHERE t2.UID=t1.UID ORDER BY t2.[DATE] DESC)

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-20 : 14:34:47

i have put square braces around DATE because DATE is a reserve word and is not suggested to use as a field name...

aks
Starting Member

28 Posts

Posted - 2005-06-20 : 15:01:28

Hi,
When I tried to run this query it always stuck. So, I have to launch my SQL again.
I dont know why?
Any idea.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-21 : 02:19:15

How many rows that table has?
Here is a method

Declare @t table(UID int, NAME varchar(20),DATE datetime, STATUS char(10),INiTIALS char(3),DAYS int)
insert into @t values(78, 'kijj', '23-may-2005', 'A', 'KJ', 23)
insert into @t values(67, 'bob', '18-June-2005', 'A', 'b', 20)
insert into @t values(78, 'kijj', '20-jun-2005', 'A', 'KJ', 0)
insert into @t values(67, 'bob', '18-jun-2005', 'O', 'B', 20)
insert into @t values(67, 'bob', '18-jun-2005', '', '', 23)

Select Distinct UID,Name,(Select max(date) from @t where UId=T.UID),
(Select max(Status) from @t where UId=T.UID),(Select max(Initials) from @t where UId=T.UID),
(Select max(days) from @t where UId=T.UID) from @t T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources