SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to extract the row corresponding to first occr
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gamaz2
Starting Member

31 Posts

Posted - 03/04/2014 :  13:04:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2014 :  13:26:18  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 03/04/2014 :  13:32:27  Show Profile  Reply with Quote
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

Edited by - Lamprey on 03/04/2014 13:33:49
Go to Top of Page

gamaz2
Starting Member

31 Posts

Posted - 03/04/2014 :  14:16:05  Show Profile  Reply with Quote
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.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1642 Posts

Posted - 03/04/2014 :  19:20:01  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000