Select latest description from table

  • I have a sales table that updated daily with new sales data. When pulling historical data for a particular SKU# and description, I get multiple lines for each description that the SKU# has been. I would like to be able to only pull the latest description from the table and use is for all sales for that SKU#

    SKU# Description SalesDate

    01110Red Car with stripe 01/19/2009

    01110Red Car with line 02/01/2009

    01110 Red Car with laser 02/25/2009

    Thanks,

    Seajoker

  • if object_id('tempdb..#t1') is not null

    drop table #t1

    create table #t1 (SKU# char(5), [Description] varchar(200), SalesDate datetime)

    Insert into #t1 ([SKU#], [Description], SalesDate) Values('01110', 'Red Car with stripe', '01/19/2009')

    Insert into #t1 ([SKU#], [Description], SalesDate) Values('01110', 'Red Car with line', '02/01/2009')

    Insert into #t1 ([SKU#], [Description], SalesDate) Values('01110', 'Red Car with laser', '02/25/2009')

    Insert into #t1 ([SKU#], [Description], SalesDate) Values('01120', 'Blue Car with stripe', '01/19/2009')

    Insert into #t1 ([SKU#], [Description], SalesDate) Values('01120', 'Blue Car with line', '02/05/2009')

    Insert into #t1 ([SKU#], [Description], SalesDate) Values('01120', 'Blue Car with laser', '02/20/2009')

    ;with cte

    AS

    (SELECT *, row_number() over(partition by [sku#] order by SalesDate desc) rn

    from #t1

    )

    select [SKU#], [Description], SalesDate

    from cte

    where rn =1


    * Noel

  • Hi,

    you need to use the row_number function to identify which specific rows you want returned. Change the order by clause if you want the oldest record.

    create table # (SKU# varchar(10), Description varchar(128), SalesDate datetime)

    insert #

    select '01110', 'Red Car with stripe', '01/19/2009'

    union all select '01110', 'Red Car with line', '02/01/2009'

    union all select '01110', 'Red Car with laser', '02/25/2009'

    union all select '01120', 'Blue Car with stripe', '01/19/2009'

    union all select '01120', 'Blue Car with line', '02/01/2009'

    union all select '01120', 'Blue Car with laser', '02/25/2009'

    select [sku#], [description], [SalesDate] from

    (select *, row_number() over (partition by SKU# order by salesDate desc) as rownum from #) x

    where rownum=1

    Bevan

  • If I add Quantity to the equation, the data looks like this :

    SKU# Description Date Quantity RN

    01110Red Car with laser2009-02-25 00:00:00.00031

    01110Red Car with line 2009-02-01 00:00:00.00032

    01110Red Car with stripe2009-01-19 00:00:00.00033

    01120Blue Car with laser2009-02-20 00:00:00.00031

    01120Blue Car with line 2009-02-05 00:00:00.00022

    01120Blue Car with stripe2009-01-19 00:00:00.00023

    How can I change all descriptions for a particular sku to the newest description, so the data looks like this: I need to keep the daily sales quantity.

    SKU# Description Date Quantity RN

    01110Red Car with laser2009-02-25 00:00:00.00031

    01110Red Car with laser2009-02-01 00:00:00.00032

    01110Red Car with laser2009-01-19 00:00:00.00033

    01120Blue Car with laser2009-02-20 00:00:00.00031

    01120Blue Car with laser 2009-02-05 00:00:00.00022

    01120Blue Car with laser2009-01-19 00:00:00.00023

  • I see Noel and I posted almost identical answers on the first question!

    Heres one way to update the values...

    --- Have a look at new and old data side by side

    select a.[sku#], a.[description], a.[SalesDate], x.[description] as new from

    (select *, row_number() over (partition by SKU# order by salesDate desc) as rownum from #) x

    inner join # a on a.sku# = x.sku#

    where rownum=1

    -- Run the update

    UPDATE a set a.[description] = x.[description]

    from

    (select *, row_number() over (partition by SKU# order by salesDate desc) as rownum from #) x

    inner join # a on a.sku# = x.sku#

    where rownum=1

    -- Have a look at new values

    select * from #

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply