Selecting a row with least creation time without using top

[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.