...

View Full Version : Selecting latest rows on a join



Keleth
05-23-2011, 07:09 PM
So if I have 2 tables: A containing a list of items, and B containing a list of flags for items, where A to B has a 1 to many relationship, what is the best way to get the latest flag when selecting all items? The only way I know how is something like:

SELECT [columns] FROM A LEFT JOIN (SELECT [columns] FROM B ORDER BY flagID DESC GROUP BY itemID) ON [join conditions]

So creating that temp table to pull out the last items, then join that. Is there a better way? Or is that how I should go?

Fumigator
05-23-2011, 10:03 PM
So you want a list of all items, each item listed just once and listing just the most recent flag? This should work (untested):



SELECT *
FROM A
JOIN B
ON A.FKEY_ID = B.ID
WHERE B.ID = (SELECT MAX(ID) FROM B AS B2 WHERE B2.FKEY_ID = A.ID)

Old Pedant
05-25-2011, 02:18 AM
I read that as saying you have these tables:

Table A
-- ID primary key
-- fieldA
-- fieldB
-- fieldC

Table B
-- flagID primary key
-- ID foreign key to A table
-- fieldX
-- fieldY

or something along those lines.

If so, then this query *might* perform better than Fumigator's answer. It would probably be worth your while to try it both ways and see which MySQL does better with.


SELECT A.field1, A.field2, A.field3, B.fieldX, B.fieldY, M.maxflagid
FROM A, B, (SELECT id, MAX(flagid) AS maxflagid FROM B GROUP BY id ) AS M
WHERE A.id = M.id
AND M.id = B.id
AND M.maxflagid = B.flagid

Keleth
05-25-2011, 03:48 PM
Ah, so either way, Id need to the temp table in the query (is it called something else? when you use parens to create a table in the question?) and then join on that. Wonderful, thanks much!

Fumigator
05-25-2011, 05:28 PM
I call 'em subqueries but I'm old school. :D

Keleth
05-25-2011, 06:29 PM
Subquery, that makes MUCH more sense then a temporary table (as you can make temporary tables in SQL, right?).

Old Pedant
05-25-2011, 09:49 PM
You can make temporary tables in *SOME* databases. They are not part of the SQL language standard, per se. They are also created quite differently in different DBs (e.g., MySQL requires explicit use of the word TEMPORARY in the CREATE TABLE statement; SQL Server just uses a special naming convention.)

I call what Fumigator and I did "SUB-SELECTs" or "SUBQUERIES" interchangeably. Terribly imprecise of me, I know.

bazz
05-25-2011, 10:46 PM
I call them derived tables :) or maybe it's better described as a correlated sub query?

Old Pedant
05-26-2011, 12:03 AM
I call them derived tables :) or maybe it's better described as a correlated sub query?

SQL Server actually *has* something known as "table-valued functions" that are closer in meaning to "derived tables", so yeah, "correlated sub query" is more generic and I'd use that.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum