Query question

[Yannick Turgeon]

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

[MC]

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" <ve********@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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

[Erland Sommarskog]

Yannick Turgeon (ve********@gmail.com) writes:

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?

Another variation:

SELECT a.data
FROM #Test a
JOIN (SELECT type, pid = MAX(pid)
FROM #Test
GROUP BY type) AS b ON a.type = b.type
AND a.pid = b.pid
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Yannick Turgeon]

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.

Yannick

[Erland Sommarskog]

Yannick Turgeon (ve********@gmail.com) writes:

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.

Maybe. If it clear what it means. Which in at least in the second
case is not at all clear. MAX(pid) in the entire #Test? MAX(pid) for
type = 'A' or what?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx