Bug #38857 SQL addition - ORLIMIT

• Submitted: 18 Aug 2008 13:45
• Modified: 19 Aug 2008 10:09
• Reporter: Lig Isler-Turmelle
• Status: Duplicate
• Category: MySQL Server: Parser
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[18 Aug 2008 13:45] Lig Isler-Turmelle

Description:
It would be nice to expand the capabilities of "limit" to include the ability to limit the individual OR statements in the WHERE clause, rather than having to twist SQL into a pretzel to get what I need.

Table to be used in Example:
 CREATE TABLE roster (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL,
 name VARCHAR(10) NOT NULL,
 role VARCHAR(10) NOT NULL,
 date DATETIME NOT NULL,
 team_id INT UNSIGNED NOT NULL,
 points INT UNSIGNED NOT NULL,
 PRIMARY KEY(id));

 INSERT INTO roster VALUES ('', 'John Black', 'coach', '2008-01-01
 00:00:01', 1, 0);
 INSERT INTO roster VALUES ('', 'Jack Black', 'coach', '2008-01-01
 00:00:02', 2, 0);

 INSERT INTO roster VALUES ('', 'Joe Smoe', 'player', '2008-01-01
 00:00:03', 1, 2);
 INSERT INTO roster VALUES ('', 'Jane Smoe', 'player', '2008-01-01
 00:00:04', 1, 3);
 INSERT INTO roster VALUES ('', 'Harry Smith', 'player', '2008-01-01
 00:00:05', 1, 1);
 INSERT INTO roster VALUES ('', 'Jay Thompson', 'player', '2008-01-01
 00:00:06', 1, 2);

 INSERT INTO roster VALUES ('', 'Philip Hass', 'player', '2008-01-01
 00:00:03', 2, 2);
 INSERT INTO roster VALUES ('', 'Julie Hass', 'player', '2008-01-01
 00:00:04', 2, 3);
 INSERT INTO roster VALUES ('', 'Janet Phillips', 'player', '2008-01-01
 00:00:05', 2, 1);
 INSERT INTO roster VALUES ('', 'Fred Clause', 'player', '2008-01-01
 00:00:06', 2, 4);
 
Example Query using the ORLIMIT capability:

SELECT name, role FROM roster WHERE role = 'player' OR role = 'coach'
ORDER BY date DESC ORLIMIT 2;

 +------------+--------+
 | name | role |
 +------------+--------+
 | Joe Smoe | player |
 | Jane Smoe | player |
 | John Black | coach |
 | Jack Black | coach |
 +------------+--------+

How to repeat:
na

[19 Aug 2008 10:09] Susanne Ebrecht

This can be done by feature request from bug #38859 as well.

Instead of: SELECT name, role FROM roster WHERE role = 'player' OR role = 'coach'
ORDER BY date DESC ORLIMIT 2;

Just:
SELECT name, role FROM roster WHERE role='player' OR role = 'coach' GROUP BY role, date, name ORDER BY date DESC LIMIT BY GROUP 2;