select 10 rows per day with order

Question

hi i have a db with records with date (timestamp) i need to select 10 records for each day (there are many more per day) and order them by few columns...

how should that query look like?

Answer 1

If you just need ten rows — any ten rows, you don't care which, and there is no guarantee they're random, you can use the LIMIT clause. Example:

SELECT whatever
  FROM tablename
  WHERE datecol = '2009-07-13'
  LIMIT 10

That'll give you ten rows. Its up to MySQL which ten. You can use ORDER BY or additional WHERE items to pick a certain 10. For example, here is the most recent 10:

SELECT whatever
  FROM tablename
  WHERE datecol = '2009-07-13'
  ORDER BY timecol DESC
  LIMIT 10

LIMIT is documented as part of the SELECT syntax.

Answer 2

If you are working with MySQL, and the column is of type timestamp. You need to convert it to Date and then compare it with the date you want to compare with.

SELECT * FROM tablename tName

where

Date(timestampFieldName) = Date('2009-07-08')

limit 0,10

Answer 3

You have to get your 10 records per day in a subquery for each day and join them to the main table by a left join, so you'll get max 10 records per day. The SQL would look like this:

SELECT t1.columns
FROM mytable t1 
  LEFT JOIN 
     (SELECT pk FROM mytable t2 
     WHERE t2.datecol = t1.datecol 
     ORDER BY t2.orderFor10Rows LIMIT 10) t3
  ON t1.pk = t3.pk
ORDER BY t1.anyOtherColumns

No warranty for proper MySQL-syntax as I'm not used to it.

Answer 4

Here is a possible solution, but it will require a little work outside of sql. This is from a live example of a list of movies. All you need to do after this is pull the first 10 movies of the concatenated list.

SELECT Movie_Release_Year, GROUP_CONCAT( Movie_Title
ORDER BY Movie_Title ) 
FROM movies
GROUP BY Movie_Release_Year

see Group Concat for more details

Answer 5

After looking at (the ever excellent) xaprb blog from Baron Schwarz, http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ I wonder if the use of user-defined variables could work here.

select hiredate, ename, sal
from ( select hiredate, ename, sal,
       @num := if(@hiredate = hiredate, @num + 1, 1) as row_number,
       @hiredate := hiredate as dummy
     from emp_temp
) as x
where row_number < 3
order by hiredate, ename, sal

I've only tried the above for small sets of data, but it seems to work in bringing back just two records per hiredate. So far as I can tell from limited testing it should scale up for greater data sets. ( there may be performance issues in large data sets as Mysql is creating a temporary table)

Answer 6

If you're working from a programming language and not directly querying the server, you could dynamically construct a query for the union of the 'Limit 10' or 'Top 10' for each day. Not incredibly efficient, but it would at least work and be easy to debug and modify later. You could even create the query dynamically via an SP in the server and work straight from there.

Answer 7

Did you ever get your question answered? I have the same problem...