Posted by MC on 11/30/05 10:30

Well, you could use max function if you think it looks better :). It is a
bit easier to read. You could test performance on both of these queries and
see if one performs any better then the other....

WHERE pid = (SELECT max(pid) FROM #Test WHERE type = 'A' )


MC


"Yannick Turgeon" <vendredi5h@gmail.com> wrote in message
news:1133293081.301995.22000@g47g2000cwa.googlegroups.com...
> 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]