CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
ahmed mohamady
ahmed mohamady

Reputation: 362

How To get the First Row Form SQL Group Query?

I have a problem in writing a query.

I'd like to select the first row of each set of rows grouped

My table is Transactions:

userID | Date     | StoreID 
---------------------------
     1 | 8-9-2013 | 10
     1 | 9-9-2013 | 10
     1 | 10-9-2013| 20
     2 | 7-9-2013 | 30
     2 | 8-9-2013 | 10
     2 | 9-9-2013 | 20
     1 | 11-9-2013| 10
     2 | 10-9-2013| 20

and I try to this SQL statement:

Select 
    tr.userID , Min(tr.TransactionDate) FirstDate
From 
    Transactions tr 
Group By 
    tr.userID

I get this output:

userID | Date     
------------------
     1 | 8-9-2013 
     2 | 7-9-2013

But I need the Store ID in every first transaction.

I need it to be like that 

 userID | Date    |  StoreID
-------------------------
     1 | 8-9-2013 |  10
     2 | 7-9-2013 |  30

Please any one can help me

Upvotes: 3

Views: 18232

Answers (5)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Transactions
    ([userID] int, [Date] datetime, [StoreID] int)
;

INSERT INTO Transactions
    ([userID], [Date], [StoreID])
VALUES
    (1, '2013-08-09 00:00:00', 10),
    (1, '2013-09-09 00:00:00', 10),
    (1, '2013-10-09 00:00:00', 20),
    (2, '2013-07-09 00:00:00', 30),
    (2, '2013-08-09 00:00:00', 10),
    (2, '2013-09-09 00:00:00', 20),
    (1, '2013-11-09 00:00:00', 10),
    (2, '2013-10-09 00:00:00', 20)
;

Query 1:

SELECT
    tr.userID , Min(tr.Date) FirstDate , tr2.storeid
FROM
    Transactions tr
inner join Transactions tr2 on tr.userid = tr2.userid and 
                              tr2.date = (select top 1 date 
                                          from transactions t 
                                          where t.userid = tr2.userid
                                          order by date asc)
GROUP BY
    tr.userID, tr2.storeid

Results:

| USERID |                     FIRSTDATE | STOREID |
|--------|-------------------------------|---------|
|      1 | August, 09 2013 00:00:00+0000 |      10 |
|      2 |   July, 09 2013 00:00:00+0000 |      30 |

Upvotes: 2

mdega
mdega

Reputation: 106

You could use Row_Number().

select UserId, Date, StoreId from  (select row_number() over(partition
by UserId order by date) as RowNumber,   UserId, Date, StoreId from
Transactions  ) as View1 where  RowNumber = 1

http://sqlfiddle.com/#!6/e536a/7

Upvotes: 4

brazilianldsjaguar
brazilianldsjaguar

Reputation: 1449

You can do this with a subquery. The basic principle is that the subquery identifies the min date for each one, and the wrapping query picks the row that matches the user and min date, also being able to return the store id.

It would look something like this:

SELECT
    t.UserID,
    t.Date,
    t.StoreId
FROM
    Transactions t JOIN
    (
        SELECT 
            tr.userID , Min(tr.Date) AS FirstDate
        FROM
            Transactions tr
        GROUP BY 
            tr.userID 
    ) u ON t.UserId = u.UserId AND t.Date = u.FirstDate

You can check this out yourself in SqlFiddle here.

Upvotes: 0

Declan_K
Declan_K

Reputation: 6826

You could use a sub-query

SELECT  TR1.userID
        ,TR1.TransactionDate
        ,TR1.StoreID
FROM    Transactions tr1
INNER JOIN
        (
        Select 
            tr.userID 
            ,Min(tr.TransactionDate) AS FirstDate
        From 
            Transactions tr 
        Group By 
            tr.userID 
        ) SQ
ON      TR1.userID = SQ.userID 
AND     TR1.TransactionDate = SQ.FirstDate

Upvotes: 3

Sonam
Sonam

Reputation: 3466

with user_cte (userid,date)
as(Select tr.userID , Min(tr.TransactionDate) FirstDate
From  Transactions tr 
Group By tr.userID 
)

select b.userid,b.date,a.storeId from Transactions a join user_cte b on a.userID=b.userId and a.Date=b.Date

Upvotes: 0

Related Questions