INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Getting Recent 3 dates

Getting Recent 3 dates

(OP)
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

RE: Getting Recent 3 dates

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

She's a Lithium Flower

RE: Getting Recent 3 dates

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.


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


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&amp;type=TD

RE: Getting Recent 3 dates

Actually it can be also written as

CODE

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

RE: Getting Recent 3 dates

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close