Select most recent from mysql table
I have two mysql tables:
Item containing items that one can buy:
CREATE TABLE `item` (
`itemid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY开发者_Python百科 (`itemid`)
) ENGINE=InnoDB;
Purchase containing all purchases:
CREATE TABLE `purchase` (
`purchaseid` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`amount` int(11) DEFAULT NULL,
`itemid` int(11) DEFAULT NULL,
PRIMARY KEY (`purchaseid`)
) ENGINE=InnoDB;
I want to select the most 20 recent purchases based on date and purchaseid and join the item table to show the name of these purchases. If an item has been purchased more than once in the 20 recent purchases it should only show up once. No duplicates. I really can't figure this out.. Maybe you can? Thanks!
Wouldn't it be:
SELECT `name`
from `item` join `purchase` using(`itemid`)
group by `itemid` order by `date` desc limit 20
OR
SELECT DISTINCT `name`
from `item` join `purchase` using(`itemid`)
order by `date` desc limit 20
Using DISTINCT
allows you to omit duplicates, as does GROUP BY
(which also allows you to perform functions on the grouped data)
精彩评论