SQL group - limit

Question

Sometimes finding the best way to communicate a 'problem' is as difficult as finding the solution... LOL but here goes...

I have a table with companies, I have a related table with some history for each company... I want to return a query with a record set of the last 3 'history' entries per company...

so the recordset will look something like this...

company A
  history Az
  history Ay
  history Ax
company B
  history Bz
  history By
  history Bx
company C
  history Cz
  history Cy
  history Cx

The issue I'm running into is if I LIMIT 3 - with the joins being in there I JUST get the LAST 3 records of all the records, not the last 3 for EACH company...

Here's what I have last tried - but it's just pulling back ONE company, 3 records.. and that's it...(IBM DB2 9 - but the only thing that should affect is the syntax on the limit of 3..)

SELECT 
   C.CompanyName   
  ,H.*

FROM Companies C
JOIN CompanyTypes CT ON C.fkTypeID = CT.ID

INNER JOIN (
    SELECT   sCH.*
    FROM     CompanyHistory sCH
    ORDER BY sCH.DATE DESC 
    FETCH FIRST 3 ROWS ONLY
) H ON H.fkCompanyID = C.ID 


WHERE CT.Type = 'bookstore'

What am I doing wrong?

Answer 1

SELECT C.CompanyName,H.*
FROM Companies C
JOIN CompanyTypes CT ON C.fkTypeID = CT.ID
INNER JOIN (
    SELECT   sCH.*
    FROM     CompanyHistory sCH    
    ORDER BY sCH.DATE DESC                   -- order desc so we can count
) H ON H.fkCompanyID = C.ID 
WHERE CT.Type = 'bookstore'
  and 3>(select count(*)                     -- at most 2 previous occurances
       from CompanyHistory ich
       where ich.fkCompanyID=C.ID            -- same company
          and datediff(d,ich.date,H.date)<0) -- but dates less than the row's date

Basically I'm counting the previous rows for each company, and dropping rows once we get past 2 (+the current row makes 3). You need to sort the CompanyHistory table by date desc for this to work.

You may need to replace datediff with the function for your SQL flavor, I only really know SQL Server.

Answer 2

It looks like DB2 supports a ROW_NUMBER() with an OVER clause. I'm taking a guess at the syntax, but hopefully this will point you in the right direction:

SELECT
    CompanyName,
    SomeColumn
FROM (
    SELECT
        C.CompanyName,
        H.SomeColumn,
        ROW_NUMBER() OVER(PARTITION BY C.ID ORDER BY CH.DATE DESC)
    FROM
        Companies C
    INNER JOIN CompanyTypes CT ON C.fkTypeID = CT.ID
    INNER JOIN CompanyHistory CH ON CH.fkCompanyID = C.ID
) AS SQ