Using SQL Server  CTE  and PARTITION BY   – the following problem was solved – instead of using GROUP BY.

 The SQL Server CTE is a temporary resultset. It’s not stored as an object – and the scope is for the query.

 In conjunction with SQL Server PARTITION BY – which filters out the recordset . Generally – PARTITION is like GROUP BY.

 The message I received from the developer was

 “I am creating a new universe that holds transaction data and for the key element there are sequential records which hold the changes.  For example

 Element            Date                 Sequence         

A                      25/01/2011        1                     

A                      26/01/2011        2

A                      29/01/2011        3

A                      02/02/2011        4

B                      01/01/2011        1

B                      07/02/2011        2

 I am trying to create a situation where we can create a report that will show the latest information for each element on a specific date.

 So if filtering on 31/01/2011, a report will just pick up the latest Element on or before this date.

A                      29/01/2011        3

B                      01/01/2011        1

“

This is  the response in abbreviated form :

SET DATEFORMAT dmy

GO

CREATE TABLE #test

(element VARCHAR(1),

date_entry datetime,

sequence INT

)

GO

INSERT INTO #test

SELECT 'A','25/01/2011',1

UNION

SELECT 'A','26/01/2011',2

UNION

SELECT 'A','29/01/2011',3

UNION

SELECT 'A','02/02/2011',4

UNION

SELECT 'B','01/01/2011',1

UNION

SELECT 'B','07/02/2011',2

 

GO

 

WITH element_CTE (element,date_entry,sequence,ranking)

AS

(select element,date_entry,sequence, Rank() over

(PARTITION BY Element order by Sequence DESC) as Rank

from #test

WHERE date_entry < '31/01/2011'

)

select * from element_CTE  where ranking = 1

 GO