CODEWITHSUNDEEP
mysqlsql-order-bylimitgreatest-n-per-group
mike_haney
mike_haney

Reputation: 545

selecting top 3 rows in an ordered select

i have table data like this:

id,time,otherdata
a,1,fsdfas
a,2,fasdfag
a,3,fasdfas
a,7,asfdsaf
b,8,fasdf
a,8,asdfasd
a,9,afsadfa
b,10,fasdf
...

so essentially, i can select all the data in the order i want by saying something like:

select * from mytable ordered by id,time;

so i get all the records in the order i want, sorted by id first, and then by time. but instead of getting all the records, i need the latest 3 times for each id.

Answer:

Well, I figured out how to do it. I'm surprised at how quick it was, as I'm operating on a couple million rows of data and it took about 11 seconds. I wrote a procedure in a sql script to do it, and here's what it looks like. --Note that instead of getting the last 3, it gets the last "n" number of rows of data.

use my_database;

drop procedure if exists getLastN;
drop table if exists lastN;

-- Create a procedure that gets the last three records for each id
delimiter //
create procedure getLastN(n int)
begin
  # Declare cursor for data iterations, and variables for storage
  declare idData varchar(32);
  declare done int default 0;
  declare curs cursor for select distinct id from my_table;
  declare continue handler for not found set done = 1;
  open curs;

  # Create a temporary table to contain our results
  create temporary table lastN like my_table;

  # Iterate through each id
  DATA_LOOP: loop

  if done then leave DATA_LOOP; end if;
  fetch curs into idData;
  insert into lastThree select * from my_table where id = idData order by time desc limit n;

  end loop;
end//

delimiter ;
call getLastN(3);
select * from lastN;

sorry if this doesn't exactly work, I've had to change variable names and stuff to obfuscate my work's work, but i ran this exact piece of code and got what i needed!

Upvotes: 0

Views: 25121

Answers (3)

wwkudu
wwkudu

Reputation: 2796

Two approaches that I'm aware of are (1) to use a set of unions, each one containing a "limit 3", or (2) to use a temporary variable. These approaches, along with other useful links and discussion can be found here.

Upvotes: 1

pSub
pSub

Reputation: 1

Try this:

select *
from mytable as m1
where (
   select count(*) from mytable as m2
   where m1.id = m2.id
) <= 3 ORDER BY id, time

Upvotes: 0

Halcyon
Halcyon

Reputation: 57719

I think it's as simple as:

SELECT * FROM `mytable`
GROUP BY `id`
ORDER BY `time` DESC
LIMIT 3

Upvotes: 7

Related Questions