most recent values - 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)

most recent values

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-10 : 05:36:00

I'm sure something similar to this must have been asked before but I can't see how to do it. I am using SQL server 7 and need to extract the most recent event from a table as follows

My table shows clinic's that patients have attended so would have the fields

ClinicNumber, PatientNumber, ClinicDate

What I want is a query that will pull the most recent clinic that the patient attended BUT I also need the ClinicNumber in the results.

What I have is

SELECT Patientnumber, max(clinicdate)
from ClinicTable
GROUP BY PatientNumber

which gives what I want apart from the clinic number

Can anyone help?

thanks

steve

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-10 : 08:13:22

There are many ways to write this query. One would be


select
    patientnumber,
    clinicnumber
from
    clinictable ct
where
    not exists (
        select *
        from
            clinictable
        where
            clinicdate > ct.clinicdate)

Jay White
{0}

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-02-10 : 09:18:35


select
    patientnumber,
    clinicnumber
from
    clinictable ct
where
    not exists (
        select *
        from
            clinictable
        where
            clinicdate > ct.clinicdate
        and
            patientnumber = ct.patientnumber)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-10 : 10:13:55

oh yeah ...

Jay White
{0}

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-02-10 : 10:25:07

I would try this as well to see if the query plan looks any better; certainly there are situations where it will:


SELECT ClinicNumber, PatientNumber, ClinicDate
FROM ClinicTable AS A
WHERE ClinicDate = (
    SELECT MAX(ClinicDate)
    FROM ClinicTable AS B
    WHERE A.PatientNumber = B.PatientNumber
  )

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-10 : 10:56:45

Thanks to all that replied, I now have a working system

steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources