CODEWITHSUNDEEP
sqlfirebird
Robert
Robert

Reputation: 43

Two rows for each element in a query

I've got two simple tables in Firebird 2.1:

Invoices: InvocesID, ClientID, .....

and

Client: ClientID, GroupID, Name .......

When I select

 SELECT   
       I.*   
 FROM 
       INVOICES I  
       LEFT JOIN CLIENT C ON C.ClientId=I.ClientID  
 WHERE 
    C.GroupID = 15  // for instance

Then I've got all invoices. How can I get only 2 invoices for each client in groupid =15?

Upvotes: 0

Views: 71

Answers (1)

Livius
Livius

Reputation: 956

The sipmplest (not so optimal solution but working in any Firebird version especially your ancient 2.1) is:

SELECT
    I.*
FROM
    CLIENT C
    INNER JOIN INVOICES I ON C.ClientId = I.ClientID AND I.InvocesID >=
    (SELECT FIRST 1 SKIP 1 I2.InvocesID FROM INVOICES I2 WHERE I2.ClientId = I.ClientID ORDER BY I2.InvocesID DESC)
WHERE
    C.GroupID = 15

i have assumed that your newest invoice have bigger number. Also i have changed left join to inner join as you are interested in invoices. If you also need to return clients with no invoices at all change it to LEFT JOIN

If you need not 2 but e.g. 4 invoices simply change SKIP 1 to SKIP 3

Upvotes: 1

Related Questions