Simplify a complex query III

richard101 2014-06-10 11:52:08

excuse my ignorance but what does this even mean?? Surely it doesn't take 2 select statements to return one column on 1 condition?
richard101 2014-06-10 12:13:38
Thanks Mart & John

I still feel there must be a simpler way to returning the FloorPlanID column where the associated row number is 1.

richard101 2014-06-10 12:50:21
Hi all

With the benefit of the actual data … I suspect this may be equivalent …

SELECT max(FloorPlanID)
FROM dbo.tblFloorPlan
group by MasterFloorPlanID

Mart 2014-06-10 13:21:18
Hi Richard

Cool, always easier to play when you have some data and know the constraints of it 😛

Nice one!

Mart 2014-06-10 13:31:05
If it does turn out you need to stick with the original idea for whatever reason, you could potentially write it with a CROSS APPLY

 
                SELECT      dbo.tblFloorPlan.FloorPlanID,
                            RN.RowNumber
                FROM        dbo.tblFloorPlan WITH (NOLOCK) 
                                INNER JOIN dbo.tblMasterFloorPlan WITH (NOLOCK)
                                ON dbo.tblFloorPlan.MasterFloorPlanID = dbo.tblMasterFloorPlan.MasterFloorPlanID
                                CROSS APPLY (SELECT Row_Number () OVER(Partition By  dbo.tblMasterFloorPlan.MasterFloorPlanID order by  dbo.tblFloorPlan.FloorPlanID DESC) As RowNumber) RN
                WHERE RN.RowNumber = 1
richard101 2014-06-10 13:58:06
Thanks Mart
Mart 2014-06-10 12:00:30
Hi again Richard

The query is generating a row number using the Row_Number function in the first query then the second is only selecting where the row number is 1.

I'd be included to re-write this so the first part is a sub select of the second instead of using the temp table or a CTE could be used.

Take a look here for some more info:
http://msdn.microsoft.com/en-us/library/ms186734.aspx

Mart 2014-06-10 12:05:31
    SELECT      RN.FloorPlanID,
                RN.RowNumber
    FROM 
            (
                SELECT      dbo.tblFloorPlan.FloorPlanID,
                            Row_Number () OVER(Partition By  dbo.tblMasterFloorPlan.MasterFloorPlanID order by  dbo.tblFloorPlan.FloorPlanID DESC)   As RowNumber
                FROM        dbo.tblFloorPlan WITH (NOLOCK) 
                                INNER JOIN dbo.tblMasterFloorPlan WITH (NOLOCK)
                                ON dbo.tblFloorPlan.MasterFloorPlanID = dbo.tblMasterFloorPlan.MasterFloorPlanID
            ) AS RN


WHERE RN.RowNumber = 1
Mart 2014-06-10 12:06:40
Not tested but re-written like this would get rid of the temp table.
Mart 2014-06-10 12:10:47
Guess I should mention the use of WITH (NOLOCK) which, although doesn't lock when doing the read can end up missing or reading data twice as page splits occur.

There are many articles on it, here's just one: http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

John M Couch 2014-06-10 12:02:04
You have no filter on your query so there are essentially two table scans going on and a hash match join to merge the data together. Because of the partition you are creating in the select clause and the order by, the next piece is a sort to order the data by FloorplanID. The next 3 pieces are the segment, sequence project and top which are all the partitioning by MasterFloorPlanID and row_number pieces. It appears that is being inserted into a temp table then and then retrieved.