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())
- NOW() function gives you the current date. So we search for only those dates, which are more than current date.
- Out of the searched dates, we get the minimum (earliest) date using MIN() function.
- 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
Insert Distinct Values from One Table into Another Table
How to Display Half Records from Table - MySQL
Using Node.Js (Express) and MySQL to Insert for a Timestamp
Laravel Update Multiple Records At Once
Multiple Query Same Table But in Different Columns MySQL
Addition of Total Hours in SQL Server
Remove Numbers from String SQL Server
Multiple Rows Output into Variables in MySQL
How to Remove Special Characters in Column With MySQL
Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause
Show Zero If There Is No Record Count - Oracle SQL Query
Formatting Numbers by Padding With Leading Zeros in SQL Server
Sql: Returning the Most Common Value for Each Person
Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )
Better Techniques for Trimming Leading Zeros in SQL Server