开发者

Select most recent from mysql table

开发者 https://www.devze.com 2023-01-19 15:18 出处:网络
I have two mysql tables: Item containing items that one can buy: CREATE TABLE `item` ( `itemid` int(11) NOT NULL AUTO_INCREMENT,

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)

0

精彩评论

暂无评论...
验证码 换一张
取 消