getting 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)

getting latest data

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 21:45:29

Hi friends
i have table with following fields(actually i've more flds)
refdate --- datetime
masterid --- FK (int)
referralid -- PK (int)

what i want is ,i need to write sql stmt that gets latest record(i.e. refdate) for a master (i.e. masterid )

if i have data like this
masterid,refdate ,referralid
4, 2002-10-22 12:34:20.000, 4122
5, 2002-12-20 10:16:26.000, 4123
4, 2002-12-20 10:24:44.000, 4124
4, 2002-12-20 10:47:29.000, 4125

i want to see data like this
5, 2002-12-20 10:16:26.000, 4123
4, 2002-12-20 10:47:29.000, 4125

how can do that please ?
Thanks for ur ideas

Cheers

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-20 : 21:52:21

try to create the query using select with max and group by

--------------------
keeping it simple...

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-20 : 21:54:00

SELECT MAX(refdate) FROM <your table>
WHERE masterID = <some masterID>

That'll return the latest record....is that what you wanted?? *shrug*
--Nick

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 21:57:12

actually latest record for each master (ie. FK).
i sort of achieved what i want

SELECT * FROM referrals
INNER JOIN
(SELECT max(refdate) AS refdate,max(ref.masterid) AS mastid
FROM referrals AS ref GROUP BY ref.masterid) ref
ON referrals.refdate = ref.refdate AND referrals.masterid = ref.mastid

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 21:58:49

but my query may fail bcoz if there is same date happens to be there for same master.
i mean if i have data like this
4, 2002-12-20 10:24:44.000, 4124
4, 2002-12-20 10:24:44.000, 4125

i want one record only ,in this case 4125 .how can i change my query ?

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 21:59:54

Or something like this

select t.*
from   table t
inner join 
(
    select masterid, max(refdate) as max_refdate
    from   table
    group by masterid
) m
on  t.masterid = m.masterid
and t.refdate  = m.max_refdate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 22:05:08

Just saw your last post.
Is the referralid allocated in sequence ?
Try this

select t.*
from   table t
inner join 
(
    select masterid, max(refdate) as max_refdate, max(referralid) as max_referralid 
    from   table
    group by masterid
) m
on  t.masterid    = m.masterid
and t.refdate     = m.max_refdate
and t.referralid  = m.max_referralid

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 22:07:25

kh
i believe thats what my query is (see my post abv)
but what if have same date for 2 records for same master?
abv query will display two records ,wouldn't it ?

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 22:13:21

KH
thats wonderful. thanks
btw, what happens if i have data like this
4, 2005-12-22 10:24:44.000, 4124
4, 2002-12-20 10:24:44.000, 4125

would it give me 1st record or 2nd ? actually 1st one though

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 22:14:16

>> Is the referralid allocated in sequence ?

yes it is

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 22:50:52

Is the referralid unique ?

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 22:54:04

yup,it is.

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 22:56:24

[code]declare @table table
(
masterid int,
refdate datetime,
referralid int
)
insert into @table
select 4, '2002-10-22 12:34:20.000', 4122 union all
select 5, '2002-12-20 10:16:26.000', 4123 union all -- *
select 4, '2002-12-20 10:24:44.000', 4124 union all
select 4, '2002-12-20 10:47:29.000', 4125 union all -- *
select 6, '2005-12-22 10:24:44.000', 4126 union all -- *
select 6, '2002-12-20 10:24:44.000', 4127

select t.*
from @table t
inner join
(
select masterid, max(referralid) as max_referralid
from @table a
where refdate = (select max(refdate) from @table x where x.masterid = a.masterid)
group by masterid
) m
on t.masterid = m.masterid
and t.referralid = m.max_referralid

/* RESULT
masterid refdate referralid
----------- ------------------------------------------------------ -----------
4 2002-12-20 10:47:29.000 4125
5 2002-12-20 10:16:26.000 4123
6 2005-12-22 10:24:44.000 4126
*/
[/code]

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-20 : 23:10:18

Thanks KH. that worked nicely :)

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources