Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-01-24 : 07:18:15
|
hello theresee below dataPurchaserID tranid TranHistID date80 10338936 729716 2008-04-28 18:38:30.24780 11297765 863767 2009-07-01 14:20:55.11780 11420179 863768 2009-07-01 14:20:55.57080 15215786 1032517 2011-07-21 13:20:23.30381 332931 37783 1998-08-05 13:49:49.94381 332923 37783 1998-08-05 13:49:49.94382 220351 664 1997-11-25 09:30:56.14082 465162 34106 1998-06-24 11:38:05.02082 865634 63543 1999-02-10 13:34:50.980how do i select the first entry by date for each purchaser, while selecting all columnsRegardsRob |
|
sqlbay
Starting Member
12 Posts |
Posted - 2013-01-24 : 07:36:39
|
Hi,If date column is of Date/Datetime type,try thisselect PurchaserID,tranid,TranHistID,date from (Select *,row_number(partition by PurchaserID,tranid,TranHistID order by date) min_dtfrom Your_Table)t1where t1.min_dt=1SQL Server Professional http://sqlbay.blogspot.in |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-24 : 08:25:57
|
quote: Originally posted by sqlbay Hi,If date column is of Date/Datetime type,try thisselect PurchaserID,tranid,TranHistID,date from (Select *,row_number() over (partition by PurchaserID,tranid,TranHistID order by date) min_dtfrom Your_Table)t1where t1.min_dt=1SQL 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
550 Posts |
Posted - 2013-01-24 : 08:29:31
|
hello therei have putselect PurchaserID,transactionid,TransactionHistoryID,TransactionDate from (Select *,row_number(partition by PurchaserID,transactionid,TransactionHistoryID order by TransactionDate) min_dtfrom Ticket_Highest_TranHist)t1where t1.min_dt=1and i am getting the below error.Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'by'.the column names are different because ive shortened them or the post |
|
|
sqlbay
Starting Member
12 Posts |
Posted - 2013-01-24 : 08:38:09
|
Please check the corrected query @webfred..Thnx for correction. Query will give u the first entry ie. oldest dateSQL Server Professional http://sqlbay.blogspot.in |
|
|
|
|
|
|
|