Sql Grammar for Select Min(Date)
I Have a Table with Structure: Id(Int Pk), Title(Varchar), Date(Date) How Do I Select All Distinct Titles with Their Earliest Date? Apparently, Select Distinct...
I have a table with structure:
id(INT PK), title(VARCHAR), date(DATE)
How do I select all distinct titles with their earliest date?
Apparently, SELECT DISTINCT title, MIN(date) FROM table doesn't work.
7 Answers
You need to use GROUP BY instead of DISTINCT if you want to use aggregation functions.
SELECT title, MIN(date)
FROM table
GROUP BY title
An aggregate function requires a GROUP BY in standard SQL
This is "Get minimum date per title" in plain language
SELECT title, MIN(date) FROM table GROUP BY title
Most RDBMS and the standard require that column is either in the GROUP BY or in a functions (MIN, COUNT etc): MySQL is the notable exception with some extensions that give unpredictable behaviour
You are missing a GROUP BY here.
SELECT title, MIN (date) FROM table GROUP BY title
Above should fix this. And you don't even need a DISTINCT now.
If you want to get updated records then you can use the following query.
SELECT title, MAX(date) FROM table GROUP BY title
SELECT MIN(Date) AS Date FROM tbl_Employee /*To get First date Of Employee*/
To get the titles for dates greater than a week ago today, use this:
SELECT title, MIN(date_key_no) AS intro_date FROM table HAVING MIN(date_key_no)>= TO_NUMBER(TO_CHAR(SysDate, 'YYYYMMDD')) - 7
SELECT MIN(t.date)
FROM table t
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
