Selecting a row with least creation time without using top

Prasad A

Hi,
I want to know if there is any way to retrieve an entire row that has a column with least creation time without using top.

Let us take a sample table named Patient

PatientId Observation Time Value

1 Temp 2014-02-19 03:55:00 35
1 Temp 2014-02-19 03:45:00 37
1 Weight 2014-02-19 03:40:00 60

If i am given the PatientId and Observation, I need to retrieve the the row with minimum creation time
For patient id 1 and observation Temp this would be the row
1 Temp 2014-02-19 03:45:00 37
since it has 2014-02-19 03:45:00 as minimum creation time

Jorgen Andersson

WITH FirstObservation AS (
SELECT PatientID,Observation,Min(Time) Time
FROM MyTable
WHERE PatientID = @PatientID
AND Observation = @Observation
GROUP BY PatientID,Observation
)
SELECT PatientId,Observation,TIME,Value
FROM MyTable m
JOIN FirstObservation f
ON m.PatientID = f.PatientID
AND m.Observation = f.Observation
AND m.Time = f.Time

Wrong is evil and must be defeated. - Jeff Ello[^]

Kornfeld Eliyahu Peter

Why not using order by and top? Because that is can be fast and effective, specially when you have an index on time column? Or just because it's an exercise you got at school?

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Lost User

Kornfeld Eliyahu Peter wrote:

Why not using order by and top?

Just a guess; "it's too slow"

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Kornfeld Eliyahu Peter

And nested select not? It's performance depends only on the proper indexing...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Simon_Whale

Damn you Eddy you made try to learn something today!

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

Lost User

Kornfeld Eliyahu Peter wrote:

And nested select not?

I did not say that :)

Kornfeld Eliyahu Peter wrote:

It's performance depends only on the proper indexing...

..and the amount of records, the amount of fields in a row, their size, and the speed of the harddisk. Ya reckon there's an index on the date? :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Kornfeld Eliyahu Peter

Eddy Vluggen wrote:

and the amount of records, the amount of fields in a row, their size

Not quiet right... With indexed table there will be two page reads only (and if the index clustered only one), not matter what size the table is...

Eddy Vluggen wrote:

Ya reckon there's an index on the date

I do not think there is a table at all - it sound me like a school exercise...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Lost User

Create a table called "Cheater", containing both PatientId and last updatetime. Set both from a trigger on the Patient-table - do an insert of it doesn't contain the patient-Id, otherwise simply update the time. Index the table on time, id. You now have an indexed int/date table for your lookup.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Jorgen Andersson

Maybe it's just not for an SQLserver. If you want a generic query TOP is out of the question.

Wrong is evil and must be defeated. - Jeff Ello[^]

Yes Yes Yes

Hi, I am a user of MySql. In Mysql we can go for the query

select * from Patient where time=(select max(time) from Patient);

Hope it will work for you. Check n update.