How to order in SQL by name with latest id?-CodePudding

I have a problem. I have big data table with 300 users (as garazas_id) for every user I have multiple rows created over the past 3 years. I will order it by garazas_id and will see only record with latest id for every row (or with latest date it will be the same result).

This is my SQL code:

SELECT id = MAX(id), garazas_id, sk_rad, datums 
FROM `elektr_apmaks`
GROUP BY garazas_id 
ORDER BY garazas_id   0;

Order is ok, but results in each row is with the earlier id.

CodePudding user response：

It seems you may be using MySQ, the following should work in all versions of that

select
       ea.*
from `elektr_apmaks` as ea
inner join (
    SELECT
           max(id) as id
         , garazas_id 
    FROM `elektr_apmaks`
    GROUP BY garazas_id
    ) as mx on ea.id = mx.id
ORDER BY garazas_id

IF you cAN use row_number() over() (if you have V8 or later of MySQL)

select
       ea.*
from (
    SELECT
           *
         , row_number() over(partition by garazas_id 
                             order by id DESC) as rn
    FROM `elektr_apmaks`
    GROUP BY garazas_id
    ) as ea
where rn = 1
ORDER BY garazas_id

CodePudding user response：

You can do the following as well if date matters:

select TOP 1 * from TableName order by dateColumn desc;