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

回答1:

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 |

回答2:

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

回答3:

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

回答4:

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

回答5:

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.