Selecting minimum date from postgres table using group by clause

Question

I have a table with data as company name, employee name, leave days. I want to extract the company name and min date for an employee. For this I am using this query

select companyname, min(date) from table where companyname = 'apple' and employeename = 'ABC'

This query however fails saying that column "tablename.companyname" must appear in the GROUP BY clause or be used in an aggregate function.

I did try using companyname in group by clause along with date but this gave me a lot of values instead of one minimum date.

Can any one figure out how to do this without adding to complexities.

I did figure out the solution. However, I didn't quite understand why it asked me to use companyname in the group by clause.

Answer 1

It is standard SQL requirement that you GROUP BY columns that are not aggregated.

SELECT companyname, min(date) AS min_date
FROM   tbl
WHERE  companyname = 'apple'
AND    employeename = 'ABC'
GROUP  BY companyname;

You can use a positional reference to shorten the code in Postgres:

...
GROUP  BY 1;

Or drop the pre-determined column companyname from the SELECT list. Then you don't need GROUP BY either:

SELECT min(date) AS min_date
FROM   tbl
WHERE  companyname = 'apple'
AND    employeename = 'ABC';