By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,597 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

Query question

P: n/a
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

Nov 29 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 30 '05 #2

P: n/a
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
Nov 30 '05 #3

P: n/a
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

Dec 1 '05 #4

P: n/a
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
Dec 1 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.