| Author |
 Topic  |
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
 Posted - 01/24/2013 : 07:18:15
|
hello there
see below data
PurchaserID tranid TranHistID date
80 10338936 729716 2008-04-28 18:38:30.247
80 11297765 863767 2009-07-01 14:20:55.117
80 11420179 863768 2009-07-01 14:20:55.570
80 15215786 1032517 2011-07-21 13:20:23.303
81 332931 37783 1998-08-05 13:49:49.943
81 332923 37783 1998-08-05 13:49:49.943
82 220351 664 1997-11-25 09:30:56.140
82 465162 34106 1998-06-24 11:38:05.020
82 865634 63543 1999-02-10 13:34:50.980
how do i select the first entry by date for each purchaser, while selecting all columns
Regards
Rob |
|
|
sqlbay
Starting Member
12 Posts |
Posted - 01/24/2013 : 07:36:39
|
Hi,
If date column is of Date/Datetime type,try this
select PurchaserID,tranid,TranHistID,date from
(Select *,row_number(partition by PurchaserID,tranid,TranHistID order by date) min_dt
from Your_Table)t1
where t1.min_dt=1
SQL Server Professional http://sqlbay.blogspot.in |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 01/24/2013 : 08:25:57
|
quote:
Originally posted by sqlbay
Hi,
If date column is of Date/Datetime type,try this
select PurchaserID,tranid,TranHistID,date from
(Select *,row_number() over (partition by PurchaserID,tranid,TranHistID order by date) min_dt
from Your_Table)t1
where t1.min_dt=1
SQL Server Professional http://sqlbay.blogspot.in
And: What is the first? the oldest or thje newest date?
Too old to Rock'n'Roll too young to die. |
|
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
Posted - 01/24/2013 : 08:29:31
|
hello there
i have put
select PurchaserID,transactionid,TransactionHistoryID,TransactionDate from
(Select *,row_number(partition by PurchaserID,transactionid,TransactionHistoryID order by TransactionDate) min_dt
from Ticket_Highest_TranHist)t1
where t1.min_dt=1
and i am getting the below error.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'by'.
the column names are different because ive shortened them or the post |
|
|
|
sqlbay
Starting Member
12 Posts |
Posted - 01/24/2013 : 08:38:09
|
Please check the corrected query @webfred..
Thnx for correction. Query will give u the first entry ie. oldest date
SQL Server Professional http://sqlbay.blogspot.in |
|
|
| |
Topic |
|
selecting earliest entry in list
