CODEWITHSUNDEEP
mysqlsql
jarosik
jarosik

Reputation: 4564

SQL max with additional column

I want to select a latest record from MySQL table using MAX. Date column that I apply MAX to is selected properly, but second column the ID is not. Following screenshot expalins this best:enter image description here

As you see 1_2018 is selected instead of 15_2018.

Upvotes: 1

Views: 82

Answers (3)

Sinto
Sinto

Reputation: 3997

SELECT created, year_id
FROM business_trip 
WHERE created = (SELECT MAX(created) FROM business_trip);

In this case I'm here using sub query to satisfy your requirement. Other answers used ORDER BY (which will not give the expected answer until the year_id is MAX) and other with MAX() in SELECT will not pull the related record with latest created value. In my query I'm also avoided use of check over year_id so this will always return you the record with latest created value.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You could use your query as a sub query to pick the row(s) for max created value

select *
from business_trip
where created = (
    select max(created )
    from business_trip
    where year_id LIKE '%_2018'
)

Upvotes: 1

ughai
ughai

Reputation: 9880

you can sort by the created and then LIMIT 1 to get only a single record. Something like this

SELECT created,year_id
FROM business_trip 
WHERE year_id LIKE '%_2018'
ORDER BY created DESC LIMIT 1;

Upvotes: -1

Related Questions