MySQL multiple LIMIT 1 queries on 1 table

Question

I have a table like this:

id
name
type - enum( A, B, C)
datetime

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 ;

Gordon Linoff · Accepted Answer · 2013-05-27 14:37:38Z

2

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.

answered May 27, 2013 at 14:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

DaGLiMiOuX Over a year ago

Isn't this the same as GROUP BY? =/ (i.e.) SELECT name, type, max(datetime) FROM table GROUP BY type; and you don't "overload" server with multiple queries

Gordon Linoff Over a year ago

@DaGLiMiOuX . . . I'm not sure what you mean. If you mean doing a group by on the outer query only, then "NO". Such a query is not guaranteed to get the right name.

DaGLiMiOuX Over a year ago

See my updated comment. I gave you an example. It's the same as you are doing with a subquery.

fancyPants Over a year ago

Like Gordon said, your query can not guarantee to get the right name. See this manual entry: dev.mysql.com/doc/refman/5.5/en//…

fancyPants Over a year ago

You don't group by name, just by type. So MySQL chooses one of the names of each type group. This may not necessarily be the one where datetime is the greatest. You output max(datetime), but the name may not be the corresponding row.

|

Collectives™ on Stack Overflow

MySQL multiple LIMIT 1 queries on 1 table

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related