Home / Questions / 20331
Accepted
Miquel Coll
Asked: 2020-08-12 23:13:47 +0800 CST 2020-08-12 23:13:47 +0800 CST

Get unaggregated SQL query related fields

  • 772

Given the query:

SELECT MAX(fecha), estado
FROM tabla
GROUP BY estado

I get the maximum date of the table for each state.

My question is: Can I get the columns that are not in the Group By without doing aggregations? namely:

SELECT MAX(fecha), estado, id
FROM tabla
GROUP BY estado

Where I would like the id to be the concrete id of the row being selected with MAX(fecha).

If the table tablacontains:

id - date - status

1 - 03/11/1991 - Open

2 - 03/11/1992 - Open

3 - 03/11/1993 - Open

4 - 03/11/1992 - Closed

5 - 03/11/1991 - Closed

If you applied the first query you would get:

03/11/1993, Open

03/11/1992, Closed

And I would like to get:

03/11/1993, Open, 3

03/11/1992, Closed, 4

I understand that there is an extra problem in the event that more than 1 row has the same date and status, which would cause the id to be random but any would do. That is, if the table contains:

id - date - status

1 - 03/11/1991 - Open

2 - 03/11/1992 - Open

3 - 03/11/1993 - Open

4 - 03/11/1992 - Closed

5 - 03/11/1991 - Closed

6 - 03/11/1992 - Closed

The result is very useful for me:

03/11/1993, Open, 3

03/11/1992, Closed, 4

Like this:

03/11/1993, Open, 3

03/11/1992, Closed, 6

But if there's a better way to work with it I'd like to learn it.

sql

2 Answers

  1. Best Answer

    Your first query is perfect, but to retrieve the rest of the fields, you have to do a JOINwith the original table.

    SELECT tmax.*
FROM tabla tmax
INNER JOIN
  (SELECT MAX(fecha) max_fecha, estado
   FROM tabla
   GROUP BY estado) tgroup
ON 
  tmax.fecha = tgroup.max_fecha
  AND tmax.estado = tgroup.estado
GROUP BY tmax.estado;

    Demo en SQLfiddle

    • And if we remove the last one group by, we would obtain all the rows that meet (2 rows with status Cerradothat meet the condition would appear -since they are the same with different IDs). (*commented by the author)
    • 2

  2. This already depends on your data model, but if you are in the case that your id is auto incrementing, it would be enough to do a MAX(id)and thus you would obtain the last one. However, the engine cannot assure you that the retrieved id matches the MAX(fecha).

    • -1