CODEWITHSUNDEEP
sqlfirebirdgreatest-n-per-groupfirebird2.5
Marcoscdoni
Marcoscdoni

Reputation: 985

Firebird Query- Return first row each group

In a firebird database with a table "Sales", I need to select the first sale of all customers. See below a sample that show the table and desired result of query.

---------------------------------------
SALES
---------------------------------------
ID  CUSTOMERID  DTHRSALE
1   25          01/04/16 09:32
2   30          02/04/16 11:22      
3   25          05/04/16 08:10
4   31          07/03/16 10:22
5   22          01/02/16 12:30
6   22          10/01/16 08:45

Result: only first sale, based on sale date.

ID  CUSTOMERID  DTHRSALE
1   25          01/04/16 09:32
2   30          02/04/16 11:22      
4   31          07/03/16 10:22
6   22          10/01/16 08:45

I've already tested following code "Select first row in each GROUP BY group?", but it did not work.

Upvotes: 2

Views: 4447

Answers (4)

Mark Rotteveel
Mark Rotteveel

Reputation: 109136

In Firebird 2.5 you can do this with the following query; this is a minor modification of the second part of the accepted answer of the question you linked to tailored to your schema and requirements:

select   x.id,
         x.customerid, 
         x.dthrsale
    from sales x
    join (select customerid,
                 min(dthrsale) as first_sale
            from sales 
        group by customerid) p on p.customerid = x.customerid
                              and p.first_sale = x.dthrsale
order by x.id

The order by is not necessary, I just added it to make it give the order as shown in your question.

With Firebird 3 you can use the window function ROW_NUMBER which is also described in the linked answer. The linked answer incorrectly said the first solution would work on Firebird 2.1 and higher. I have now edited it.

Upvotes: 2

bh_earth0
bh_earth0

Reputation: 2825

in Firebird 3 , get first row foreach customer by min sales_date :

SELECT id, customer_id, total, sales_date
FROM   (
   SELECT id, customer_id, total, sales_date
        , row_number() OVER(PARTITION BY customer_id ORDER BY sales_date ASC ) AS rn
   FROM   SALES
   ) sub
WHERE  rn = 1;

İf you want to get other related columns, This is where your self-answer fails.

select customer_id , min(sales_date) 
           , id, total  --what about other colums
from SALES
group by customer_id

Upvotes: 0

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

Search for the sales with no earlier sales:

SELECT S1.*
FROM SALES S1
LEFT JOIN SALES S2 ON S2.CUSTOMERID = S1.CUSTOMERID AND S2.DTHRSALE < S1.DTHRSALE
WHERE S2.ID IS NULL

Define an index over (customerid, dthrsale) to make it fast.

Upvotes: 0

Marcoscdoni
Marcoscdoni

Reputation: 985

So simple as:

select CUSTOMERID min(DTHRSALE) from SALES group by CUSTOMERID

Upvotes: -1

Related Questions