0

I have the following SQL Query :

 SELECT DISTINCT a.project_id, a.acc_name, a.project_name, a.iot,a.ilc_code, a.active, a.license_no, c.line_id, c.chargable_fte, c.cost_call_date from Account a INNER JOIN account_version c USING (acc_id) where a.acc_name='APMM' AND EXTRACT (MONTH FROM c.cost_call_date)>=1 AND EXTRACT (MONTH FROM c.cost_call_date)<=4 AND EXTRACT (YEAR FROM c.cost_call_date)>=2018 AND EXTRACT (YEAR FROM c.cost_call_date)<=2018 order by c.line_id desc;

It is giving the following results :

 project_id   acc_name  project_name  iot  ilc_code  active  license_no  line_id  chargable_fte  cost_call_date
 IN-16-10171   APMM      DAMCO        LA    AGP7GL    false               24        70             2018-04-03
 IN-16-10171   APMM      DAMCO        LA    AGP7GL    false               23        70             2018-04-03
 IN-16-10171   APMM      DAMCO        LA    AGP7GL    false               13        68             2018-03-20
 IN-16-10171   APMM      DAMCO        LA    AGP7GL    false               11        65             2018-03-20

Here, the column cost_call_date has the date in the format yyyy-mm-dd. I want to get the data which has the maximum line_id for a particular month. I want to get only one row for each month. Also, I don't want the line_id column in my result. That is, the required result would be as follows :

 project_id   acc_name  project_name  iot  ilc_code  active  license_no  chargable_fte  cost_call_date
 IN-16-10171  APMM       DAMCO        LA    AGP7GL    false                  70            2018-04-03
 IN-16-10171  APMM       DAMCO        LA    AGP7GL    false                  68            2018-03-20

Please help me achieve this result.

Kkunal
  • 1
  • 5
  • `max(date) group by date_trunc('month',date)`... show us some code – Vao Tsun Apr 04 '18 at 08:49
  • 1
    Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) or others shown under "Related questions" such as [this](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column?rq=1), [this](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1), or [this](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1). Or browse the [tag:greatest-n-per-group] tag. – IMSoP Apr 04 '18 at 09:21
  • Asked many time: https://stackoverflow.com/questions/tagged/postgresql+greatest-n-per-group –  Apr 04 '18 at 09:41

0 Answers0