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)
 select rows only with min date

Author  Topic 

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2014-04-11 : 14:24:32
I am trying to get only the rows where the earliest dates for each account number from a table which looks like below.

AcctNo BatchDate code Amount
FN123 3/1/2014 1 20
FN123 3/1/2014 2 40
FN123 3/1/2014 3 20
FN234 3/1/2014 1 10
FN456 3/1/2014 1 5
FN123 3/20/2014 1 20
FN123 3/20/2014 2 10
FN123 4/10/2014 2 10



the output i am expecting is

AcctNo BatchDate code Amount
FN123 3/1/2014 1 20
FN123 3/1/2014 2 40
FN123 3/1/2014 3 20

please suggest.
Thanks,

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-11 : 15:20:50
quote:
Originally posted by sreenu9f

I am trying to get only the rows where the earliest dates for each account number from a table which looks like below.

AcctNo BatchDate code Amount
FN123 3/1/2014 1 20
FN123 3/1/2014 2 40
FN123 3/1/2014 3 20
FN234 3/1/2014 1 10
FN456 3/1/2014 1 5
FN123 3/20/2014 1 20
FN123 3/20/2014 2 10
FN123 4/10/2014 2 10



the output i am expecting is

AcctNo BatchDate code Amount
FN123 3/1/2014 1 20
FN123 3/1/2014 2 40
FN123 3/1/2014 3 20

please suggest.
Thanks,

Your description and the sample output does not match. In the output you are picking up on AccountNo = FN123. So the following is only a guess.
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY AcctNo ORDER BY BatchDate) AS RN
FROM
YourTable
)
SELECT
AcctNo, BatchDate,
ROW_NUMBER() OVER (ORDER BY AcctNo) AS code,
Amount
FROM
cte
ORDER BY
AcctNo;
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-12 : 00:05:28
May be this is the one you want ........


DECLARE @Temp TABLE(AcctNo VARCHAR(100),BatchDate DATE,Code INT , Amount INT)
INSERT INTO @Temp VALUES ('FN123','3/1/2014',1,20)
,('FN123','3/1/2014',2,40)
,('FN123','3/1/2014',3,20)
,('FN234','3/1/2014',1,10)
,('FN456','3/1/2014',1,5)
,('FN123','3/20/2014',1,20)
,('FN123','3/20/2014',2,10)
,('FN123','4/10/2014',2,10)
--SELECT * FROM @Temp
SELECT * FROM
(SELECT AcctNo,(CASE WHEN MIN(BatchDate) OVER (PARTITION BY AcctNo)= BatchDate THEN BatchDate END) AS BatchDate,Code,Amount FROM @Temp WHERE AcctNo ='FN123' ) AS a WHERE a.BatchDate IS not NULL




---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -