Choose 10 rows per day with order

Question

I have a db with records with a date (timestamp) I need to select 10 records for each day (there are many more per day) and arrange them in several columns ...

What should this query look like?

Answer 1

You should receive 10 records per day in the subquery for each day and join them in the main table using the left join, so you will receive a maximum of 10 records per day. SQL will 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 

There is no guarantee on the correct MySQL syntax, as I'm not used to it.

Answer 2

To select the top 10 records for every day, sorted by day in SQL Server 2005

 select * from (select *,ROW_NUMBER() OVER (PARTITION BY record.day order by record.day desc,record.score desc) as row from record) as table1 where row < 11 

Now, since your record table does not have a day column as such, you need to use something like datepart(day, record.date) instead of record.day

This should solve your problem.

Answer 3

If you need only ten lines - any ten lines, you do not care what kind of guarantee that they are random, you can use the LIMIT . Example:

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

This will give you ten lines. Its up to MySQL, which is ten. You can use ORDER BY or optional WHERE elements to select a specific 10. For example, here are the very last 10:

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

LIMIT documented as part of the SELECT syntax.

Answer 4

If you work with a programming language, rather than directly requesting a server, you can dynamically build a request to combine "Limit 10" or "Top 10" for each day. Not incredibly effective, but at least it will work and will be easy to debug and modify later. You can even create a request dynamically through the SP on the server and work right from there.

Answer 5

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 6

Here is a possible solution, but it will take a little work outside sql to work. This is from a live movie list example. All you have to do after this is pull out the first 10 films in the combined 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 7

After watching the (ever excellent) xaprb blog by Baron Schwarz, http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ I wonder if it can work here user variables.

 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 just tried the above for small datasets, but it seems to work with only returning two records for each employee. As far as I can tell from limited testing, it should increase for a larger data set. (performance problems may occur in large datasets, since Mysql creates a temporary table)

Answer 8

Actually, it looks more like I lost order on the date.

Here we assume that the record table has a datetime field and a rating field for ranking records.

 SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY datepart(year, Record.date), datepart(month, Record.date), datepart(day, Record.date) ORDER BY Record.score desc) AS row FROM Record ) AS table1 WHERE row < 11 ORDER BY datepart(year, Record.date) desc, datepart(month, Record.date) desc, datepart(day, Record.date) desc 

Answer 9

old question, but I found a great answer (not my own) using session variables.

Given a table

 CREATE TABLE `report` ( `id` int(11) NOT NULL AUTO_INCREMENT, `reportDate` int(11) DEFAULT NULL, `count` int(11) DEFAULT NULL, `parameter` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `INDEX1` (`reportDate`,`parameter`,`count`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; 

The next query will select the top 10 options per day, depending on their number on that day.

 SELECT parameter, reportDate, count FROM (SELECT parameter, reportDate, count, @reportDate_rank := IF(@current_reportDate = reportDate,@reportDate_rank + 1, 1) AS reportDate_rank, @current_reportDate := reportDate FROM report ORDER BY reportDate, count DESC ) dateRanked WHERE reportDate_rank <= 10;