How to extract the row corresponding to first occr

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

General SQL Server Forums

New to SQL Server Programming

How to extract the row corresponding to first occr

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2014-03-04 : 13:04:16

Hi,
I have a sample table as follows:
ID ITEM_EFFECTIVE_DT ITEM_TERM AMOUNT
1067381 18-Jan-14 2141 -200
1067381 02-Feb-14 2141 -274.82
1087668 10-Jan-14 2141 1238
1087668 10-Jan-14 2141 -1238
1087668 10-Jan-14 2141 309.5
1087668 10-Jan-14 2141 -309.5
1097540 20-Jan-14 2141 -3814.16
1097540 25-Feb-14 2141 -100
1103377 27-Feb-14 2141 -200.6
1103377 31-Jan-14 2141 -19.24

I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.
1067381, 1087668, 109754, 1103377.
How does one handle this. I have not been able to come up with the sql yet. I appreciate any help. Thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-04 : 13:26:18

Not exactly sure what you meant by First Occurence of the corresponding ID. If you meant it with respect to the item_effective_dt date value in ascending order, may be following is what you're looking for

;With FirstOccur
AS
(SELECT ID,ITEM_EFFECTIVE_DT,ITEM_TERM,AMOUNT,Row_Number() Over (Partition By ID Order by Item_effective_dt) Rno
FROM TableName
)
SELECT * FROM FirstOccur WHERE rno=1

Cheers
MIK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-04 : 13:32:27

I'm also assuming the same thing Mik is. His solution is perfectly valid, perhaps even preferred. However, I'm going to take step back to show yo some steps that might help you when working with sets.

(again assuming you want the first by date), step one Identify all the IDs and The Earliest date:

SELECT ID, MIN(ITEM_EFFECTIVE_DT) AS ITEM_EFFECTIVE_DT
FROM Table
GROUP BY ID

Once you have that information (assuming there are no duplicate ID, Date combination in your table, then you can join back to your original table to get the other columns you want:

SELECT 
	* -- Replace with Column List
FROM 
	Table
INNER JOIN
	(
		SELECT ID, MIN(ITEM_EFFECTIVE_DT) AS ITEM_EFFECTIVE_DT
		FROM Table
		GROUP BY ID
	) AS T
	ON Table.ID = T.ID
	AND Table.ITEM_EFFECTIVE_DT = T.ITEM_EFFECTIVE_DT

gamaz2
Starting Member

31 Posts

Posted - 2014-03-04 : 14:16:05

Thanks to both of your for the help. I appreciate it. The issue I still have is because the ID and date combination have duplicates. With this situation I am having issues.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-03-04 : 19:20:01

What is the issue? If you want both of the records returned when the ID and date are duplicated, change the "row_number()" to "rank()".

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources