How to Select Oldest Date from MySQL

How to select oldest date from MySQL

You can order by the date field in your database. For oldest:

SELECT * FROM table WHERE condition ORDER BY dateField ASC LIMIT 1

For two oldest:

SELECT * FROM table WHERE condition ORDER BY dateField ASC LIMIT 2

etc, etc, ...

Select the earliest and latest dates

This works with the data you provided, can't say it would work in MySQL, but it works in SQL Server.

select t.in_click, 
t.first_name,
t.create_date
from tracker t
where
t.create_date = (select min(create_date) from tracker where in_click = t.in_click)
or t.create_date = (select max(create_date) from tracker where in_click = t.in_click)

Get the oldest datetime value with mysql

If you are entries are date based then you can use Min operator

SELECT products_id,MIN(date_added) AS OldDate
FROM products_to_categories group by products_id

The above query will select the min of date for all products_id..

Select earliest date of future dates in MYSQL

Previous version of this answer was non-performant , especially on large tables. It was not using any index whatsoever. Here is a better approach:

SELECT DATE(start_date)
FROM your_table_name
WHERE start_date >= CURRENT_DATE + INTERVAL 1 DAY
ORDER BY start_date ASC
LIMIT 1
  • This can utilise an index on start_date column.
  • It short-circuits using LIMIT 1 , as soon as the result is found.

Previous version (don't use):

Try this:

SELECT MIN(DATE(start_date)) 
FROM table
WHERE DATE(start_date) > DATE(NOW())
  1. NOW() function gives you the current date. So we search for only those dates, which are more than current date.
  2. Out of the searched dates, we get the minimum (earliest) date using MIN() function.
  3. DATE() function extracts the date part out of a date/datetime expression (eg: result of NOW()) to yyyy-mm-dd format (eg: '2018-07-27').

Get the latest date from grouped MySQL data

Are you looking for the max date for each model?

SELECT model, max(date) FROM doc
GROUP BY model

If you're looking for all models matching the max date of the entire table...

SELECT model, date FROM doc
WHERE date IN (SELECT max(date) FROM doc)

[--- Added ---]

For those who want to display details from every record matching the latest date within each model group (not summary data, as asked for in the OP):

SELECT d.model, d.date, d.color, d.etc FROM doc d
WHERE d.date IN (SELECT max(d2.date) FROM doc d2 WHERE d2.model=d.model)

MySQL 8.0 and newer supports the OVER clause, producing the same results a bit faster for larger data sets.

SELECT model, date, color, etc FROM (SELECT model, date, color, etc, 
max(date) OVER (PARTITION BY model) max_date FROM doc) predoc
WHERE date=max_date;

Get the value of the earliest and the latest date in mysql

First of all, the day name can actually be found using the DAYNAME() function, so there is actually no need to store it in another column. But anyways, try this code.

SELECT 
user_id,
DAYNAME(MIN(DateUpdate)) as first_day,
DAYNAME(MAX(DateUpdate)) as last_day
FROM tbl
GROUP BY user_id

You could also remove the DAYNAME() function if you just want the dates.

MySQL SELECT whole oldest day from DATETIME

You just need to select the oldest day (in a subquery) and join that back to the table.

To select the oldest day:

select date(min(material_cas)) from material where material_referenceid = 13

So, using that:

select m.material_id, m.material_cas
from (
select date(min(material_cas)) oldest_date from material where material_referenceid = 13
) oldest
join material m on m.material_reference_id=13 and date(m.material_cas)=oldest_date

Assuming material_cas is stored in UTC, to look based on the Europe/Prague date, do instead:

select m.material_id, m.material_cas
from (
select date(convert_tz(min(material_cas),'+00:00','Europe/Prague')) oldest_date from material where material_referenceid = 13
) oldest
join material m on m.material_reference_id=13 and date(convert_tz(m.material_cas,'+00:00','Europe/Prague'))=oldest_date

Select oldest timestamp in MySQL when grouping rows

Try selecting MAX(timestamp) as in the following. You should be using an aggregate function such as SUM, COUNT or MAX for every column selected that is not mentioned in a GROUP BY clause anyway or else the query becomes suspect or even possibly illegal depending on MySql only_full_group_by option. In your case it is just selecting timestamp from an arbitrary row.

SELECT product,
SUM(price) AS revenue,
COUNT(*) AS sales,
MAX(timestamp) as timestamp
FROM payments
WHERE status = 'Completed'
OR status = 'Pending'
OR status = 'Canceled_Reversal'
GROUP BY product


Related Topics



Leave a reply



Submit