Where Clause - 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)

Where Clause

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2004-11-03 : 20:51:16

Hi people.
i have a table which includes the following fields

stdate STFK

2004-05-13 16:29:28 sp99
2004-05-12 16:29:28 SP99
2004-05-11 15:29:28 sp99
2004-05-19 16:29:28 PR01
2004-05-13 16:29:28 PR01
i want all records for each STFK with stdate is most recent.

for ex:
2004-05-13 16:29:28 sp99
2004-05-19 16:29:28 PR01

i can do this using join statement:
select ts.* from tblstatus TS
inner join (select stfk, MAX(StDate) AS STDat
FROM dbo.tblStatus
GROUP BY StFK) as T
ON
TS.STdate = t.stdat
and
TS.stfk=t.stfk

which working very fine. but i want to do this using Where clause cos i have to use it as filter in one of my publication.

iam using this query:

select ts.* from tblstatus ts
where stdate in (select MAX(StDate)
FROM dbo.tblStatus
GROUP BY StFK )

but this gives
2004-05-13 16:29:28 sp99
2004-05-19 16:29:28 PR01
2004-05-13 16:29:28 PR01
as some STFK has same time

please help
Cindy

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-03 : 21:05:58

select ts.* from tblstatus ts
where stdate = (select MAX(StDate)
FROM dbo.tblStatus t2 where t2.STFK = ts.STFK)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-03 : 22:54:00

select max(stdate),stfk from tblstatus
group by stfk
having stfk=criteria

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources