Jump to content
  • Advertisement
Sign in to follow this  
benryves

[web] Retrieve only the latest item (SQL - MySQL)

This topic is 4341 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Suppose you have three tables.
  • Items - a list of items, each with an ID.
  • People - a list of people, each with an ID.
  • Xref - a list of cross references, containing an Item ID, People ID and date.
I want to return a record set of all the Items, with the latest (according to the date in the Xref table) User - even if one doesn't exist. I've tried all sorts of trickery with JOIN and GROUP BY (to only return a single instance of each Item) along with MAX(Xref.Date), all to no avail. How can you do this? EDIT: I should have mentioned, MySQL 3.23. [Edited by - benryves on September 20, 2006 12:13:14 PM]

Share this post


Link to post
Share on other sites
Advertisement
I don't currently have access to a DB. Would something like this work?


SELECT *, MAX(Xref.Date) as limit
FROM Items
LEFT JOIN
Xref ON Xref.ItemID = Items.ID
WHERE
Xref.Date = limit

Share this post


Link to post
Share on other sites
Quote:
Original post by tstrimp
Does the database you're using support sub-queries?
This is MySQL 3.2, so no. There is a page on it here - thanks a lot!

Share this post


Link to post
Share on other sites
Quote:
Original post by benryves
Quote:
Original post by tstrimp
Does the database you're using support sub-queries?
This is MySQL 3.2, so no. There is a page on it here - thanks a lot!


This is a fairly tough problem I've had to do before, and the only solutions I've found use subqueries or user defined functions. Doing it in MySQL 3 (which doesn't even know what UNION is!) seems impossible at the face of it, unless you actually build and populate a temporary table through a series of commands.

Share this post


Link to post
Share on other sites
Quote:
Original post by tstrimp

SELECT *, MAX(Xref.Date) as limit
FROM Items
LEFT JOIN
Xref ON Xref.ItemID = Items.ID
WHERE
Xref.Date = limit
Unfortunately not. Unknown column 'limit' in 'where clause'.

Having the MAX() function in there means that I need to GROUP BY, and I group by Items.ID. This doesn't take the latest date into consideration, so even a HAVING Xref.Date = limit doesn't work.

EDIT: I do realise LIMIT is a keyword, and I didn't use it in the query.

Share this post


Link to post
Share on other sites
Quote:
Original post by Michalson
Doing it in MySQL 3 (which doesn't even know what UNION is!) seems impossible at the face of it, unless you actually build and populate a temporary table through a series of commands.


Does MYSQL 3 understand views?

Edit: Looks like they are new to 5.0.

Share this post


Link to post
Share on other sites
This can be done in MSSQL

First the XREF sub sub query to get the latest for each item


SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID


Then join to the XREF table again


SELECT ItemID, PeopleID
FROM Xref
INNER JOIN (
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
) LatestXref
ON Xref.ItemID = LatestXref.ItemID
AND Xref.RefDate = LatestXref.LatestRefDate


finally LEFT JOIN it to Items


SELECT Items.ItemID, ISNULL(LastPerson.PeopleID, 0) AS LatestPersonID
FROM Items
LEFT OUTER JOIN (
SELECT ItemID, PeopleID
FROM Xref
INNER JOIN (
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
) LatestXref
ON Xref.ItemID = LatestXref.ItemID
AND Xref.RefDate = LatestXref.LatestRefDate
) LastPerson
ON Items.ItemID = LastPerson.ItemID


For the above to work I'm assuming that Xrefs primary key is ItemID, PeopleID, RefDate

Share this post


Link to post
Share on other sites
select
Items.*,
People.*,
Xref.*
from
Items,
People,
Xref
where
Xref.Item_id = Item.id and
Xref.People_id = People.id
order by
Xref.date desc limit 10

?

Share this post


Link to post
Share on other sites
Quote:
Original post by Jack9
select 
Items.*,
People.*,
Xref.*
from
Items,
People,
Xref
where
Xref.Item_id = Item.id and
Xref.People_id = People.id
order by
Xref.date desc limit 10
?
That would not return items that do not have an owner on the cross-reference table.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!