I need to search in a database like this, where "id" is the variable with autoincrement:
id device 6 1 5 1 4 2 3 2 2 1 1 1
I need to sort by "device" but it gave me when "id" is minimal, I need to group with "device" when "id" is maximum
I used:
SELECT * FROM 'table' GROUP BY device
Answer he gave:
id device 1 1 3 2
Desired answer:
id device 6 1 4 2
Make SELECT of the id of your table grouped by device after obtaining that SELECT , do another SELECT on the same table obtaining the results of a INNER JOIN using the first SELECT , where you will filter the records by the id obtained in the first query.
You should have something like this:
SELECT A.* FROM 'table' A
INNER JOIN (
SELECT MAX(id) AS id FROM 'table' GROUP BY device
) B ON A.id = B.id
ORDER BY A.device
WHAT I DID
You should achieve this by using an aggregation function such as
MAX()applied to the columnidand then grouped bydeviceremaining of this mode
SELECT MAX(id), device from tabla group by device;
The only thing you should replace is tabla by the name of your table
If what you want is to show the rest of the columns, it would be enough for you to do the following
SELECT MAX(id), device, columna3, columna4, columnaN
from tabla
group by device;
Where:
column1, column2, columnN are the names of the columns that you want to be shown in the final result of your query; enough that put the name that corresponds to each one and separate them by coma
UPDATE
If you want to show all the other columns but not write them one by one; your query should look like this
SELECT MAX(id), device, tabla.*
from tabla
group by device;
WHAT I DID
The important thing here is tabla.* where table is the name of your table and when you put .* we are telling you to bring all the columns