"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
>

> 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?

> Ok. Thanks to both of you.
>
> I think something like:
>
> SELECT a.data
> FROM #Test a
> WHERE a.type = 'A'
> GROUP BY a.type
> HAVING pid = MAX(pid)
>
> or even better:
>
> SELECT a.data
> FROM #Test a
> WHERE a.type = 'A'
> AND pid = MAX(pid)
>
> would be a good functionnality (with MAX or any other aggregate
> function) to add to transact/SQL or to SQL standard.