GROUP BY WITH ORDER BY

Asked

Viewed 165 times

1

How to make a GROUP BY in the MySQL respecting the results obtained with the ORDER BY

Ex:

ID(Primary Key) | nome | codigo | tipo
1 | Item E | 5 | 1
2 | Item D | 4 | 1
3 | Item B | 2 | 1
4 | Item C | 3 | 2
5 | Item A | 1 | 2

SELECT * FROM tabela GROUP BY tipo ORDER BY codigo ASC

Result obtained:

4 | Item C | 3 | 2
1 | Item E | 5 | 1

Expected result: 

5 | Item A | 1 | 2
3 | Item B | 2 | 1
mysql group-by order-by
  • 1

    From what I understand you need to put on your selection list tipo and the aggregation function MIN(codigo), together with the GROUP BY tipo, and not having felt this ORDER BY codigo ASC.

  • 1

    Worked well using MIN(). Thanks.

2 answers

1

Solution based on post comment:

SELECT * FROM tabela WHERE codigo IN (SELECT MIN(codigo) FROM tabela GROUP BY tipo) ORDER BY codigo ASC

1

Use the clause NOT EXISTS to return only results that do not have another record with the same tipo but with codigo minor.

Schema (Mysql v5.7)

CREATE TABLE tabela (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  nome   VARCHAR(100),
  codigo INT,
  tipo   INT
);

INSERT INTO tabela(id, nome, codigo, tipo)
            VALUES(1, 'Item E', 5, 1),
                  (2, 'Item D', 4, 1),
                  (3, 'Item B', 2, 1),
                  (4, 'Item C', 3, 2),
                  (5, 'Item A', 1, 2);

Query

SELECT *
  FROM tabela t
 WHERE NOT EXISTS(SELECT 1
                    FROM tabela t2
                   WHERE t2.tipo = t.tipo
                     AND t2.codigo < t.codigo)
 ORDER BY t.codigo;

Upshot

| id  | nome   | codigo | tipo |
| --- | ------ | ------ | ---- |
| 5   | Item A | 1      | 2    |
| 3   | Item B | 2      | 1    |

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will BE TRUE, and NOT EXISTS will BE FALSE

See working on DB Fiddle.

  • select with HAVING returns nothing. Obs: GROUP BY has to come before HAVING

Browser other questions tagged mysql group-by order-by

You are not signed in. Login or sign up in order to post.