Mysql – Selecting first N rows for each group

greatest-n-per-groupgroup byMySQL

Assume I have a table like this:

+----+-----------+-----------+
| id | file_name | file_type |
+----+-----------+-----------+
|  1 | file1     | 1         |
|  2 | file2     | 1         |
|  3 | file3     | 1         |
|  4 | file4     | 2         |
|  5 | file5     | 2         |
|  6 | file6     | 2         |
|  7 | file7     | 3         |
|  8 | file8     | 3         |
|  9 | file9     | 3         |
+----+-----------+-----------+

I'm trying to get 2 rows for each file_type, so I'm following this hints:

I'm trying the following query:

SELECT file_name, file_type,
  @type_number := IF(@current_type = file_type, @type_number + 1, 1) AS type_number,
  @current_type := file_type
FROM test
ORDER BY file_type

But I'm getting the following results:

+-----------+-----------+-------------+----------------------------+
| file_name | file_type | type_number | @current_type := file_type |
+-----------+-----------+-------------+----------------------------+
| file1     | 1         |           1 | 1                          |
| file2     | 1         |           1 | 1                          |
| file3     | 1         |           1 | 1                          |
| file4     | 2         |           1 | 2                          |
| file5     | 2         |           1 | 2                          |
| file6     | 2         |           1 | 2                          |
| file7     | 3         |           1 | 3                          |
| file8     | 3         |           1 | 3                          |
| file9     | 3         |           1 | 3                          |
+-----------+-----------+-------------+----------------------------+

As you can see the type_number column should have values from 1 to 3, but it is 1 on every rows.

Where I am wrong?

Best Answer

It's a little bit more complicated, you must assign each variable @type_number and @file_type again.

SELECT file_type, file_name, id
FROM (
      SELECT  @type_number := IF(@file_type = file_type, @type_number + 1, 1) AS type_number, 
              @file_type := file_type as file_type, 
              file_name, id
      FROM 
          (SELECT @type_number := 1) x, 
          (SELECT id, file_name, @file_type := file_type as file_type FROM ttest ORDER BY file_type) y
    ) z
WHERE type_number <= 2;

Check it here: http://rextester.com/IUPWT52140

Related Solutions

Mysql – trigger in MySQL to disallow multiple row deletions at a time on a table

This goes against all that righteous and good in triggers but here we go...

Create a table that keep a count of attempted deletes per connection

CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MyISAM;

or to reduce disk I/O make it a MEMORY table

CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MEMORY;

Now get the count from that table and check it

delimiter \\
create trigger not_more_than_2_rows  
before delete on  employee
begin
declare count int;
insert ignore into trigger_happy_employee_deletes (conn_id) values (connection_id());
select delete_count into count
    from trigger_happy_employee_deletes
    where conn_id = connection_id()
;
set count = count + 1;
update trigger_happy_employee_deletes
    set delete_count = count
    where conn_id = connection_id()
;
if (count>1) then  
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
end \\
delimiter ;

After your issue a DELETE command, you will have to remember to clear the count like this:

delete from employee where ... ;
update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;

or you count clear it before:

update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;
delete from employee where ... ;

Give it a Try !!!

POTENTIAL DRAWBACKS

Here are the pitfalls of doing this:

Pitfall #1

Using an arbitration method like this (via a MyISAM table) will cause a major bottleneck because a MyISAM performs a full table lock on each INSERT. Using a MEMORY table will not fare much better because although there is far less table access on disk, there is still nominal disk I/O hitting the MEMORY table's .frm file that can cause a slight bottleneck when given enough database traffic.

You cannot make trigger_happy_employee_deletes InnoDB because triggers do not work intelligently with InnoDB:

Jun 21, 2011 : Using MySQL triggers or transactions?
Mar 21, 2011 : Dynamic SQL in MySQL stored routines

Pitfall #2

From a coding perspective, you will have to remember to setup this mechanism across your code. If you forget to do this in all necessary places, you will have undesired deletions.

Pitfall #3

If you want this same mechanism for another table, you would either make another trigger_happy table for deletes or be tempted to merge all delete triggers into a common trigger_happy table, which would make maintenance a total nightmare, not to mention increased bottlenecks by an order of magnitude. Even worse, you may be tempted to merge update and insert triggers into the common trigger_happy table.

Pitfall #4

The trigger would have to communicate with another table outside the triggered table. That's more internal read traffic just to maintain a count.

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.