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,

---

;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;

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