Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Help: Filter Data

Answered SQL Help: Filter Data

  • Monday, March 18, 2013 11:04 PM
     
     
    Sign In to Vote
    0

    Hi all, I need help on the below please

    DECLARE @Trend TABLE

    (
    RowNumberValue int,
    CatName nvarchar(100),
    Name nvarchar(100),
    Colour nvarchar(20),
    Trend nvarchar(20),
    DatetofTime nvarchar(20)


    )

    INSERT INTO @Trend
    VALUES  (1, 'Data 1','Jay', 'Red', Null,'16/03/2013')
    , (2, 'Data 1', 'Jay','Red', 'level','17/03/2013')
    , (3, 'Data 1','Jay', 'Amber','Lower','18/03/2013')
    , (1, 'Data 2','Jay', 'Red', Null,'15/03/2013')
    , (2, 'Data 2','Jay', 'Green', 'lower','16/03/2013')
    , (3, 'Data 2','Jay', 'Green', 'level','17/03/2013')
    , (1, 'Data 2','Ryan', 'Green', 'level','17/03/2013')
    , (2, 'Data 2','Ryan', 'Green', 'level','18/03/2013')


    RowNumberValue CatName  Name   Colour     Trend     DatetofTime
    1                           Data 1      Jay       Red         NULL     16/03/2013
    2                           Data 1      Jay       Red         level      17/03/2013
    3                           Data 1      Jay       Amber     Lower   18/03/2013
    1                           Data 2      Jay       Red         NULL     15/03/2013
    2                           Data 2      Jay       Green     lower     16/03/2013
    3                           Data 2      Jay       Green     level       17/03/2013
    1                           Data 2      Ryan    Green     level       17/03/2013
    2                           Data 2      Ryan     Green    level       18/03/2013

    I would like the below. basically for each CatName i.e. lets take Data 2 as an example I want for each name for Data 2 to find the max date for each name and the previous value for that name
    So Data 2 has Jay and Ryan as the names, for Jay it has 3 records, I only want the latest or max of that name (17/03/2013) and the previous value above it (16/03/2013) and show that


    RowNumberValue CatName  Name   Colour     Trend     DatetofTime
    2                           Data 1      Jay       Red         level      17/03/2013
    3                           Data 1      Jay       Amber     Lower   18/03/2013

    2                           Data 2      Jay       Green     lower     16/03/2013
    3                           Data 2      Jay       Green     level       17/03/2013

    1                           Data 2      Ryan    Green     level       17/03/2013
    2                           Data 2      Ryan     Green    level       18/03/2013

    please help

     

All Replies

  • Monday, March 18, 2013 11:16 PM
     
     Answered Has Code
    Sign In to Vote
    1
     
    ;With cte As
(Select RowNumberValue, CatName, Name, Colour, Trend, DatetofTime,
  Row_Number() Over (Partition By CatName, Name Order By DatetofTime Desc) As rn
From @Trend)
Select RowNumberValue, CatName, Name, Colour, Trend, DatetofTime
From cte
Where rn <= 2
Order By CatName, DatetofTime;

    Tom
    • Marked As Answer by Milli_22 Monday, March 18, 2013 11:24 PM
    •