Getting Recent 3 dates 1

[Learning123]

Hi Everyone,
I was wondering if anyone know a way to query on getting recent 3 dates in a table for example:

NAME PRODUCT DATE_OF_SELL
JEFF HANDSOAP 12/31/2007
JEFF HANDSOAP 10/01/2007
JEFF HANDSOAP 9/26/2007
JEFF HANDSOAP 9/03/2007
JEFF HANDSOAP 5/01/2007
JEFF HANDSOAP 4/23/2007
MARY GUM 12/27/2007
MARY GUM 12/22/2007
MARY GUM 11/29/2007
MARY GUM 11/20/2007
MARY GUM 11/23/2007

The return would be:

NAME PRODUCT DATE_OF_SELL
JEFF HANDSOAP 12/31/2007
JEFF HANDSOAP 10/01/2007
JEFF HANDSOAP 9/26/2007
MARY GUM 12/27/2007
MARY GUM 12/22/2007
MARY GUM 11/29/2007

I was thinking about about sorting it and counting the flagging the first 3. but I couldn't get it to count only the first 3. Any help would be greatly appreciated. Thank you very much for your help

She's a Lithium Flower

 

[Learning123]

ok, solved it. knew it was a simple function. ROW_NNUMBER() is a lifesaver!

She's a Lithium Flower

 

[bkclaw113]

From my experience one of the most powerful and most underused areas of SQL is ordered analytical functions.

The code below uses the order analytical rank function to provide the results that you were looking for. I know it may seem like overkill since you figured out a more basic way to accomplish the same thing but trust me once you get familiar with these functions you will be amazed with what you can do.

[tt]
Select
name
, product
, date_of_sell
From
(
Select
name
, product
, date_of_sell
, rank() Over (Partition By name, product Order By date_of_sell) as order_of_sell
From
YOUR_TABLE
) SUB1
Where
order_of_sell <= 3
[/tt]

This link should take you to the teradata SQL manual that contains the documentation on Ordered Analytic Functions

http://www.info.teradata.com/eDownload.cfm?itemid=072600012&type=TD

 

[joedsilva]

Actually it can be also written as

    Select
        name
        , product
        , date_of_sell
    From
        YOUR_TABLE
        QUALIFY rank() Over (Partition By name, product Order By date_of_sell) <= 3
;

 

[bkclaw113]

Thanks joedsilva, obviously there is still more that I need to learn about using the order analytic functions, I have never used a QUALIFY clause before (nor did I realize it was available) but I will definately be using it moving forward.