How To get the First Row Form SQL Group Query?
17,970
Solution 1
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
| USERID | FIRSTDATE | STOREID |
|--------|-------------------------------|---------|
| 1 | August, 09 2013 00:00:00+0000 | 10 |
| 2 | July, 09 2013 00:00:00+0000 | 30 |
Solution 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
Solution 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
Author by
ahmed mohamady
Updated on July 17, 2022Comments
-
ahmed mohamady almost 2 years
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