Posted by Yannick Turgeon on 11/29/05 21:38

Hello all,

Say we've got these data:

----------------------------------------
CREATE TABLE #Test (
pid INT PRIMARY KEY NOT NULL,
type CHAR NOT NULL,
data VARCHAR(10) NOT NULL
)

INSERT INTO #Test (pid, type, data)
SELECT 1, 'A', 'pizza' UNION ALL
SELECT 2, 'A', 'cake' UNION ALL
SELECT 3, 'A', 'spagetti' UNION ALL
SELECT 4, 'B', 'beer' UNION ALL
SELECT 5, 'B', 'rice' UNION ALL
SELECT 6, 'B', 'hammer'
----------------------------------------


What I'd like to get is the "data" associated whit the biggest "pid"
with "type" 'A': spaghetti.

I often face this kind of query and I'm always wondering if there is a
better way to do that. Here is the way I do this:


----------------------------------------
SELECT data
FROM #Test
WHERE pid = (SELECT TOP 1 pid FROM #Test WHERE type = 'A' ORDER BY pid
DESC)
----------------------------------------


I suspect this is not the more comprehensive way to do this. Anybody do
this differently?


Yannick

[Back to original message]