CODEWITHSUNDEEP
mysql
user2425360
user2425360

Reputation: 49

MySQL multiple LIMIT 1 queries on 1 table

I have a table like this:

The name of the types changes every X days. I want to get the last name and modify date with 1 MySQL query.

So this query:

SELECT name, type, datetime 
FROM table WHERE type = A
ORDER BY datetime DESC LIMIT 1

But then the query must return 3 rows, for every type 1. I tried to do this with an union, but that gave me an error about the order by statement. I can't figure out how to fix this issue.

Can anyone help me?

Thanks in advance.

Edit: 

CREATE TABLE IF NOT EXISTS `table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(11) NOT NULL,
`datetime` datetime NOT NULL,
`type` enum('a','b','c') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Upvotes: 1

Views: 484

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do this by filtering in the where clause with a correlated subquery:

SELECT name, type, datetime 
FROM table t
WHERE datetime = (select max(datetime)
                  from table t2
                  where t.type = t2.type
                 )

For each type, the subquery returns the maximum datetime. Rows that match the maximum for their type will be returned.

Upvotes: 2

Related Questions