SQL - Give me 3 hits for each type only
select id, title, type
from (select id, title, type,
@num := if(@group = type, @num + 1, 1) as row_number,
@group := type as dummy
from your_table
order by type, title) as x
where row_number <= 3
(Uses a different article on the same site as Martin Wickman's answer!)
mysql: select the last 10 messages and for each message the last 3 replies
A working example:
EDIT - (see revision for earlier query)
Full table creation and explain plan
Note: The table "datetable" just contains all dates for about 10 years. It is used just to generate rows.
drop table if exists messages;
create table messages (
message_id int primary key, reply_to int, createdate datetime, index(reply_to));
insert into messages
select @n:=@n+1, floor((100000 - @n) / 10), a.thedate
from (select @n:=0) n
cross join datetable a
cross join datetable b
limit 1000000;
The above generates 1m messages, and some valid replies. The query:
select m1.message_id, m1.reply_to, m1.createdate, N.N, r.*
from
(
select m.*, (
select group_concat(r.message_id order by createdate)
from messages r
where r.reply_to = m.message_id) replies
from messages m
order by m.message_id
limit 10
) m1
inner join ( # this union-all query controls how many replies per message
select 1 N union all
select 2 union all
select 3) N
on (m1.replies is null and N=1) or (N <= length(m1.replies)-length(replace(m1.replies,',','')))
left join messages r
on r.message_id = substring_index(substring_index(m1.replies, ',', N), ',', -1)
Time: 0.078 sec
Explain plan
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL (NULL) (NULL) (NULL) (NULL) 3
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 10 Using where
1 PRIMARY r eq_ref PRIMARY PRIMARY 4 func 1
4 DERIVED (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) No tables used
5 UNION (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) No tables used
6 UNION (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) No tables used
(NULL) UNION RESULT <union4,5,6> ALL (NULL) (NULL) (NULL) (NULL) (NULL)
2 DERIVED m index (NULL) PRIMARY 4 (NULL) 1000301
3 DEPENDENT SUBQUERY r ref reply_to reply_to 5 test.m.message_id 5 Using where
Mysql Query- for each date max 3 datas
SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.start_date = b.start_date AND a.event_id <= b.event_id
GROUP BY a.event_id,a.start_date
HAVING COUNT(*) <= 3
ORDER BY a.start_date
How to select only the first rows for each unique value of a column?
A very simple answer if you say you don't care which address is used.
SELECT
CName, MIN(AddressLine)
FROM
MyTable
GROUP BY
CName
If you want the first according to, say, an "inserted" column then it's a different query
SELECT
M.CName, M.AddressLine,
FROM
(
SELECT
CName, MIN(Inserted) AS First
FROM
MyTable
GROUP BY
CName
) foo
JOIN
MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted
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).
How to get multiple counts with one SQL query?
You can use a CASE
statement with an aggregate function. This is basically the same thing as a PIVOT
function in some RDBMS:
SELECT distributor_id,
count(*) AS total,
sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id
get first x rows using in()
Here ist a sample how you can do it.
1) sort all results by type
2) then count the row of each type
3) get only ROWs where recno < 3
SELECT
@nr:=IF(@lastid = e.TYPE , @nr:=@nr+1 ,1) AS recno,
@lastid:=TYPE AS last_type, e.*
FROM `example`e,
(SELECT @lastid:=0, @nr:=0) tmp
WHERE TYPE IN(3, 4)
HAVING recno < 3
ORDER BY TYPE;
Result:
MariaDB [tmp]> SELECT
-> @nr:=IF(@lastid = e.TYPE , @nr:=@nr+1 ,1) AS recno,
-> @lastid:=TYPE AS last_type, e.*
-> FROM `example`e,
-> (SELECT @lastid:=0, @nr:=0) tmp
-> WHERE TYPE IN(3, 4)
-> HAVING recno < 3
-> ORDER BY TYPE;
+-------+-----------+----+------+---------+
| recno | last_type | id | type | title |
+-------+-----------+----+------+---------+
| 1 | 3 | 1 | 3 | sth1 |
| 3 | 3 | 3 | 3 | sth2 |
| 1 | 4 | 2 | 4 | sthelse |
| 3 | 4 | 5 | 4 | sth4 |
+-------+-----------+----+------+---------+
4 rows in set (0.00 sec)
Related Topics
How to Grant Read Access for a User to a Database in SQL Server
How to Reset an MySQL Autoincrement Using a Max Value from Another Table
How to Store More Than 255 Char in MySQL Database
Join to Only the "Latest" Record with T-Sql
How to Make a Table Read Only in SQL Server
How to Create a Closure Table Using Data from an Adjacency List
Postgres - Aggregate Two Columns into One Item
Is Varchar(Max) Always Preferable
Psql: Server Closed the Connection Unexepectedly
How to Connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)
Query to List SQL Server Stored Procedures Along with Lines of Code for Each Procedure
Oracle Convert Seconds to Hours:Minutes:Seconds
Create SQL Insert Script with Values Gathered from Table