Get fields related to the SQL query not added

Question

Get fields related to the SQL query not added

Navigation

#1 by (2 votes)
#2 by (-1 votes)

2

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 making aggregations? that is:

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

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

If the tabla table contains:

id - date - state

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 additional problem in the case that more than 1 row has the same date and status, which would cause the id to be random but it would be useful to anyone. That is, if the table contains:

id - date - state

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 serves me so much this:

03/11/1993, Open, 3

03/11/1992, Closed, 4

How is this:

03/11/1993, Open, 3

03/11/1992, Closed, 6

But if there is any better way to work with it I would like to learn it.

sql

asked by Miquel Coll 12.08.2016 в 09:13

source

2 answers

-1

This already depends on your data model, but if you are in the case that your id is auto incremental, it would be enough to make a MAX(id) and thus you would get the last one. Anyway, the engine can not assure you that the recovered id matches the MAX(fecha) .

answered by 12.08.2016 в 10:51

How can I pass this to a lambda expression? Save JavaScript object in a JSON file, grunt task

score 2 · Accepted Answer

Your first query is perfect, but to recover the rest of the fields, you have to do JOIN with 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 in SQLfiddle

And if we remove the last group by , we would get all the rows that comply (2 rows with state Cerrado that meet the condition would appear -because they are the same with different ID). (* commented by the author)