Search code examples
sqlfirebirdgreatest-n-per-grouphaving

Can’t fetch a record ID field in SQL with GROUP BY clause

I have a table with structure

CUSTOMER_ID INTEGER  
QUOTE_ID    INTEGER  
VALIDFROM   DATE

Customer can have multiple quotes. I need to get list of Quote IDs where VALIDFROM DATE is MAX for each customer. Query below returns list of quotes grouped by customers and MAX VALIDFROM DATE but how can I find out QUOTE_ID of this record? Including QUOTE_ID in group clause produces wrong results

SELECT q.custid, max(q.validfrom)  
  FROM QUOTE Q  
 GROUP 
    BY q.CUSTID

Please help!

Solution

  • Hope that helps: 

    SELECT q.*
  FROM quote q
  JOIN(SELECT q.custid, max(q.validfrom)  
         FROM quote q
        GROUP 
           BY q.custid   
       ) P
     ON q.custid = p.custid
    AND q.validfrom = p.validfrom;