The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Tuesday, March 19, 2024 Login
Public

Blog posts for the month of month,2012.
Using Rank function to get newest (or oldest) record from related records.3/29/2012 12:52:57 PM

I'm slow in applying the new SQL Server functions.

Recently I ran into a request where I needed to pull the newest record for a related table several joins deep.  I finally decided to dig into and use the new rank function to do it.  It resulted in a much better query plan with many fewer reads.

Here is a simple example of using the rank function vs. using a correlated sub-query with the max() function.  In this simple example there is no cost savings either way that I could find, but real world scenarios are never this simple and you may find like I did that it is time to look at the new functions.  Example below:

use tempdb

go

Create Table ORDERS (ORDERNUMBER int identity(1,1) primary key,REFERENCE int not null,SomeBSColumns varchar(200))

go

insert into ORDERS (REFERENCE,SomeBSColumns) values (1,'order 1'),(2,'order 2'),(3,'order 3'),(4,'order 4')

go

Create Table PhoneData (PhoneKey int identity(1,1) primary key, OrderNumber int not null,NotesXML varchar(1000),TimeTag1 datetime)

go

Create nonclustered index ix_phoneData on PhoneData (orderNumber)

go

insert into PhoneData (OrderNumber,NotesXML,TimeTag1) values

(1,'Not Received','02/20/2012 16:00:00.000')

,(1,'still not received','02/22/2012 16:00:00.000')

,(1,'loud complaint','02/23/2012 16:00:00.000')

,(1,'finally received','02/24/2012 16:00:00.000')

,(3,'order 3 received, but broken','02/26/2012 16:00:00.000')

,(3,'another replacement sent','02/27/2012 16:00:00.000')

,(4,'finally received','02/22/2012 16:00:00.000')

go

-----------------

set statistics io on

go

-- Rank()

SELECT x.* FROM

( SELECT distinct o.ORDERNUMBER,o.REFERENCE, o.SomeBSColumns,

p.NotesXML,p.timetag1,

RANK() over (partition by o.ORDERNUMBER order by p.Timetag1 DESC) as 'RANKING'

FROM ORDERS o

LEFT OUTER JOIN PhoneData p ON o.ORDERNUMBER = p.ORDERNUMBER

) x

WHERE x.RANKING = 1

ORDER BY x.OrderNumber

-- old way

------

Select FIQ.*,p.NotesXML

From (

select orders.ORDERNUMBER,orders.REFERENCE, orders.SomeBSColumns,

( select MAX(TimeTag1) as TimeTag1 From PhoneData pIQ -- Alias - PhoneData Inner Query

where (ORDERS.ORDERNUMBER = pIQ.OrderNumber)

)

as TimeTag1

From ORDERS

) FIQ -- Alias = First-Inner-Query

Left Outer Join PhoneData p on

FIQ.ORDERNUMBER = p.OrderNumber

And p.Timetag1 = FIQ.TimeTag1

Deer Hill 3/28/2012 12:34:04 PM

Added a Whitney CrabApple to the North East Corner.

Whitney Crabapple

Small, sweet, edible crab apples. Pink-and-white blossoms in spring and loads of red, golf-ball-size crab apples in late August to early September. Perfect for canning, preserving, pickling and spiced apples. Flesh is sweet, juicy and slightly yellow.

 

Elderberry - (Sambucus canadensis)


Added a row of 25 Elderberry buses from MDC.

Found throughout the state in open woods, fence rows, roadsides and along borders of streams and ponds. The flowers are large, showy, flat-topped white clusters that appear in late May to June. The dark-purple berries are edible and make an excellent jelly. Many species of birds and mammals eat the fruit, and deer browse the leaves and branches. Will tolerate both wet and dry sites.

See Updated maps in previous post.

Tree layout3/19/2012 1:59:54 PM

Mostly for my own benefit so that I can remember what is planted where, we'll see what makes it.

 

 


Blog Home