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.

What was the query you used with the group by? Posting that will help people troubleshoot your issue. — Joe W
– Joe W, Commented Jun 17, 2014 at 19:25
You wanted a minimum date for a Companyname so you would want the data grouped by the Companyname and then fetch the minimum in the list. hence the Group by CompanyName :). The compiler does not care that your where clause restricts it to one CompanyName. As far as it is concerned if you have an aggregate function on a column and a column with no aggregate function. you need to tell it what to do with the column you did not put the aggregate function on. — TMNT2014
– TMNT2014, Commented Jun 17, 2014 at 19:26
@TMNT2014 : Okay. Understood. Is this exhibited only by postgres or other DBMS also? — user3527975
– user3527975, Commented Jun 17, 2014 at 21:49

Tim · Accepted Answer · 2022-05-04 16:31:24Z

4

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';

edited May 4, 2022 at 16:31

Tim

6,7915 gold badges32 silver badges24 bronze badges

answered Jun 18, 2014 at 1:09

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andriy M Over a year ago

Or maybe they want SELECT companyname, min(date) AS min_date … GROUP BY companyname ORDER BY min_date LIMIT 1;. Of course, in that case no grouping is needed

Collectives™ on Stack Overflow

Selecting minimum date from postgres table using group by clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related