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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 selecting earliest entry in list

Author  Topic 

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 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 - 2013-01-24 : 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
Go to Top of Page

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 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.
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 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
Go to Top of Page

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 date


SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page
   

- Advertisement -