vendredi 21 août 2015

SELECT First Group

Problem Definition

I have an SQL query that looks like:

SELECT *
    FROM table
    WHERE criteria = 1
    ORDER BY group;

Result

I get:

group | value | criteria
------------------------
  A   |  0    |   1
  A   |  1    |   1
  B   |  2    |   1
  B   |  3    |   1

Expected Result

However, I would like to limit the results to only the first group (in this instance, A). ie,

group | value | criteria
------------------------
  A   |  0    |   1
  A   |  1    |   1

What I've tried

Group By

SELECT *
    FROM table
    WHERE criteria = 1
    GROUP BY group;

I can aggregate the groups using a GROUP BY clause, but that would give me:

group | value
-------------
  A   |  0
  B   |  2

or some aggregate function of EACH group. However, I don't want to aggregate the rows!

Subquery

I can also specify the group by subquery:

SELECT *
  FROM table
 WHERE criteria = 1 AND 
       group = (
                       SELECT group
                         FROM table
                        WHERE criteria = 1
                        ORDER BY group ASC
                        LIMIT 1
                   );

This works, but as always, subqueries are messy. Particularly, this one requires specifying my WHERE clause for criteria twice. Surely there must be a cleaner way to do this.

Aucun commentaire:

Enregistrer un commentaire