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 
