Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Row_Number() as ID field to select the newest record Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 9:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:28 AM
Points: 116, Visits: 389
I am asking the question as I begin the research on how to do this because I am not sure if I will figure this out on my own. I have not used the Row_Number() function that often, and most of those times it was after an old mentor of mine had written it for me.

Here's what I am trying to achieve. I am re-working a nested derived table:
Select PropertyStatusDate
,comments
,rmVvlPropertyStatus
,rvlPropertyStatus.rvlpropertyid
,rvlPropertyStatusID
From dbo.rvlPropertyStatus Inner Join
(Select Max(PropertyStatusDate) As maxDate
,rvlPropertyID
From dbo.rvlPropertyStatus
Group By rvlPropertyID) As maxStatus
On rvlPropertyStatus.PropertyStatusDate = maxStatus.maxDate
And rvlPropertyStatus.rvlPropertyID = maxStatus.rvlPropertyID

In my system the joined derived table return 17,635 records, but the entire join returns 17,637 records. I believe this is being caused by 2 instances where the same property ID has two instances of the same date.

My thought is to use Row_Number() to accomplish this. I believe I know how to do this, but that is still in the conceptual phase.

My question is in two parts:
1. - The obvious question of how. I will be working on that as soon as I post this, but I don't have an exceeding level of confidence in my skills with this function yet.

2. - Is there a better means of achieving my goal? In other words what are the places where this is going to turn around and bite me that I haven't thought of yet?


“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1381154
Posted Monday, November 5, 2012 9:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:28 AM
Points: 116, Visits: 389
Ok, I have a solution for my first question. I am sure there must be a better way to do this, but this is what I've got so far:

Exec dbo.upObjectQuickDrop
@ObjectName = 'TempDataHolder'
,@ObjectType = 'Table'
,@Schema = 'TempDB';
Go

With CTE
(NestRowNumber
,rvlPropertyID)
As
(Select Row_Number() Over(Order By rvlPropertyID)
,rvlPropertyID
From dbo.rvlPropertyStatus)
Select Max(NestRowNumber) As RowNumber
,rvlPropertyID
Into #TempDataHolder
From CTE
Group By rvlPropertyID
Order By rvlPropertyID;
Go

Exec dbo.upObjectQuickDrop
@ObjectName = 'TempResultsSet'
,@ObjectType = 'Table'
,@Schema = 'TempDB';
Go

Select (Row_Number() Over(Order By rvlPropertyID)) As RowNumber
,PropertyStatusDate
,comments
,rmVvlPropertyStatus
,rvlPropertyStatus.rvlpropertyid
,rvlPropertyStatusID
Into #TempResultsSet
From dbo.rvlPropertyStatus

Select TRS.RowNumber
,PropertyStatusDate
,comments
,rmVvlPropertyStatus
,TRS.rvlPropertyID
,rvlPropertyStatusID
From #TempResultsSet TRS Inner Join #TempDataHolder TDH
On TRS.rvlPropertyID = TDH.rvlPropertyID
And TRS.RowNumber = TDH.RowNumber

Any thoughts? Any ideas where I may be overlooking a potential logic bomb?


“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1381174
Posted Monday, November 5, 2012 5:32 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
You could read about windowing function and learn how ti use them.
Your example can be solved like this:

SELECT ss.*
FROM
(
Select s.PropertyStatusDate
,s.comments
,s.rmVvlPropertyStatus
,s.rvlPropertyStatus.rvlpropertyid
,s.rvlPropertyStatusID
,rnum = ROW_NUMBER() OVER(PARTITION BY s.rvlPropertyID ORDER BY s.PropertyStatusDate DESC)
From dbo.rvlPropertyStatus s
) ss
WHERE ss.rnum = 1

Please use aliases.

Kind regards,
Vedran


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1381326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse