Get top n records for each group of grouped results
Here is one way to do this, using UNION ALL
(See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group
number and add queries for each group
:
(
select *
from mytable
where `group` = 1
order by age desc
LIMIT 2
)
UNION ALL
(
select *
from mytable
where `group` = 2
order by age desc
LIMIT 2
)
There are a variety of ways to do this, see this article to determine the best route for your situation:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
Edit:
This might work for you too, it generates a row number for each record. Using an example from the link above this will return only those records with a row number of less than or equal to 2:
select person, `group`, age
from
(
select person, `group`, age,
(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number
from test t
CROSS JOIN (select @num:=0, @group:=null) c
order by `Group`, Age desc, person
) as x
where x.row_number <= 2;
See Demo
how to get top 5 for each category
There is rank query in oracle and sql server you may search for rank equivalent in mysql. You may see here. You may also see this and this also
mySQL Returning the top 5 of each category
You have to use side effecting variables for this
SELECT profilename, name
FROM
(
SELECT m.profilename, s.name,
@r:=case when @g=m.profilename then @r+1 else 1 end r,
@g:=m.profilename
FROM (select @g:=null,@r:=0) n
cross join menus m
left join menuitems s on m.menuid = s.menuid
) X
WHERE r <= 5
Get top N rows of each group in MySQL
If you want n
rows per group, use row_number()
. If you then want them interleaved, use order by
:
select t.*
from (select t.*,
row_number() over (partition by type order by name) as seqnum
from t
) t
where seqnum <= 2
order by seqnum, type;
This assumes that "top" is alphabetically by name
. If you have another definition, use that for the order by
for row_number()
.
How to display top 5 and bottom 5 in same query with join?
If you are going to do unions with ORDER
and LIMIT
clauses, you will have to "hide" these clauses within subqueries:
SELECT * FROM (
SELECT
category_name,
members
From category c
JOIN grp g ON c.category_id=g.category_id
GROUP by category_name
ORDER BY members DESC
LIMIT 5
) SQ1
UNION
SELECT * FROM (
SELECT
category_name,
members
From category c
JOIN grp g ON c.category_id=g.category_id
GROUP by category_name
ORDER BY members
LIMIT 5
) SQ2
MySQL - 5 posts per category
Take use of session variables in MySQL since currently it doesn't support any analytic function. The subquery below will generate sequential number for each category_title
and used that column to filter in the outer query.
SELECT post_title, category_title, description
FROM
(
SELECT p.title AS post_title,
c.title AS category_title,
c.description
@counter := IF(@current_category = c.title, @counter + 1, 1) AS counter,
@current_category := c.title
FROM posts p, distributions d, categories c
WHERE p.id = d.post_id
AND d.category_id = c.id
AND d.main = 1
AND ABS(DATEDIFF(NOW(), p.created_on)) > 90
ORDER BY p.created_on DESC
) s
WHERE counter <= 5
Although the structure is not the same, but this DEMO will show you result of what the query does.
Get records with max value for each group of grouped SQL results
There's a super-simple way to do this in mysql:
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.
You avoid complicated subqueries that try to find the max()
etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)
Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.
Version 5.7 update:
Since version 5.7, the sql-mode
setting includes ONLY_FULL_GROUP_BY
by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).
Return top N rows per group in MySQL, but efficiently
The composite index that includes the grouping and ordering column will fully cover this query. Additionally, mysql will stop reading the index as soon as it finds the number of results specified in the LIMIT.
In this way, the query will not examine all the rows when it actually runs. The EXPLAIN clause is an approximation and does not include this short-circuit LIMIT optimization in its estimation for ROWS examined.
From the docs...
https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
Using index -
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
Related Topics
Hierarchical Data in Linq - Options and Performance
Custom Date/Time Formatting in SQL Server
Why Is SQL Server Throwing This Error: Cannot Insert the Value Null into Column 'Id'
Execute Stored Procedure from a Function
SQL (Oracle): Order by and Limit
How to Use Structural Annotations to Set SQL Type to Date in Model First Approach
Calculate Business Days in Oracle SQL(No Functions or Procedure)
Using SQL Function Generate_Series() in Redshift
Selecting Data into a Postgres Array
Db Design to Use Sub-Type or Not
Call a Set-Returning Function with an Array Argument Multiple Times
Insert Into... Merge... Select (SQL Server)
How to Insert Multiple Records and Get the Identity Value
SQL Multiple Columns in In Clause
Pivot Table and Concatenate Columns
Preventing Adjacent/Overlapping Entries with Exclude in Postgresql
How to Run a Stored Procedure Every Day in SQL Server Express Edition