MySQL: how to get n last rows of a certain type

Question

Putting this as simple as possible, I have the following table structure:

Date | Type | Title

Tell Type - the value is in the range of 1-10, I have 1000 entries in the table, and I want the 5 most recent entries of a unique type. So the result would be something like this:

  Date | Type | Title 2009-06-04 14:32:00 | 4 | Zeppo 2009-06-04 14:31:00 | 2 | Groucho 2009-06-04 14:30:00 | 8 | Harpo 2009-06-04 14:29:00 | 5 | Gummo 2009-06-04 14:28:00 | 3 | Chico 

It looks like I either want DISTINCT apply only to the Type column, or I need a GROUP BY that will be applied after the ORDER BY .

Everything in MySQL 4.

Answer 1

Am I missing something? An easy solution looks like:

 SELECT MAX(date) AS max_date, type, title FROM table GROUP BY type ORDER BY max_date DESC LIMIT 5 

And it should be very fast.

Answer 2

Not tested for MySQL 4 , but in MySQL 5 it can be done easily.

For this you need some kind of PRIMARY KEY in your table.

 SELECT l.* FROM ( SELECT type, COALESCE( ( SELECT id FROM mytable li WHERE li.type= dlo.type ORDER BY li.type DESC, li.date DESC, li.id DESC LIMIT 4, 1 ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid COALESCE( ( SELECT date FROM mytable li WHERE li.type= dlo.type ORDER BY li.type DESC, li.date DESC, li.id DESC LIMIT 4, 1 ), '9999-31-12') AS mdate FROM ( SELECT DISTINCT type FROM t_mytable dl ) dlo ) lo, t_mytable l WHERE l.type >= lo.type AND l.type <= lo.type AND (l.date, l.id) >= (lo.mdate, lo.mid) 

See this blog post for more details on how this works:

• Advanced row selection - how to select N rows for GROUP in MySQL .

If you cannot add a PRIMARY KEY to implement this solution, try using a less efficient one using system variables:

 SELECT l.* FROM ( SELECT @lim := 5, @cg := -1 ) vars, mytable l WHERE CASE WHEN @cg <> type THEN @r := @lim ELSE 1 END > 0 AND (@r := @r - 1) >= 0 AND (@cg := type) IS NOT NULL ORDER BY type DESC, date DESC 

It is described here:

• Row selection

Update:

If you do not want to select 5 records for each type (which will give 5 x number of types records in the result set), but instead want to select the last 5 records with a separate type (which will give 5 records in the result set), use this query:

 SELECT date, type, title FROM mytable m WHERE NOT EXISTS ( SELECT 1 FROM mytable mi WHERE mi.date > m.date AND mi.type = m.type ) ORDER BY date DESC LIMIT 5 

If you have many types, it will be more efficient if you use GROUP BY , if you have an index on date .

Answer 3

Assuming query performance is somewhat important, and you are stuck with MySQL 4, I would go something like this:

 SELECT Date, Type, Title FROM ( SELECT Date, Type, Title FROM Table WHERE Type = 1 ORDER BY Date LIMIT 1 UNION ALL SELECT Date, Type, Title FROM Table WHERE Type = 2 ORDER BY Date LIMIT 1 ... ) x ORDER BY Date LIMIT 5 

This is ugly, but you need to quickly and quickly complete the task. If you regularly add new values ​​to the type column, this may not be for you, as it requires a regular change to the query.

I think the views were added in 4.1, so you need to be in at least 4 series. If you were on version 5 or in another database, there would be more convenient ways to get closer to this.

Answer 4

This is apparently a good example for the union operator and nested samples.

Something along the lines of:

 select t.date, t.type, t.title from (Select top 5 table.Date, table.Type, table.Title from table where type = 1 order by table.date desc) as t union select t2.date, t2.type, t2.title from (Select top 5 table.Date, table.Type, table.Title from table where type = 2 order by table.date desc) as t2 [...] select t10.date, t10.type, t10.title from (Select top 5 table.Date, table.Type, table.Title from table where type = 10 order by table.date desc) as t10 order by type, date 

Unfortunately, I only have SQL Server 2008 to check this out here, so ymmv; but this is pretty simple stuff and it should work as nested selects and union are supported in mysql 4.0 according to the reference guide. (I added the as clause to the nested from the statement based on the mysql documentation.)

Answer 5

try it

 select T.* from (select max(date) dt, Type from table group by type order by dt desc limit 5) subQuery inner join table T on T.date = subQuery.dt and T.Type = subQuery.Type 

I just tried this on MySQL 5. This assumes the date field is unique. If you have a primary key other than the date, use it in the request.

Answer 6

I know that he was a little late, but had the same problem and found out how: so I send.

 SELECT t.Date, t.Type, t.Title FROM (SELECT Date, Type, Title FROM table ORDER BY Date DESC) AS t GROUP BY t.Type LIMIT 5; 

I do not think there is a simpler solution.

Explanation

• First, the first SELECT is executed. This returns a table with row desc sorted by Date column. In this table, the last record for a particular type is, first of all, other records of the same type. This return table is renamed to t.

• Now the GROUP BY operation is performed in this table based on the Type column. The result will be the first row of each type encountered.

• Now add the LIMIT clause to get the first 5 rows.