Results 1 to 3 of 3

Thread: Sql query

  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Sql query

    Hi,
    I have a table which contains three fields
    1.years
    2.item_name
    3.quantity_sold

    I want to write a query which will give me the
    best three item sold per year
    example:
    let the data is
    ****item(table name)******
    ***************************************
    years | item_name | quantity_sold
    ****************************************
    2001 | moniter | 25000000
    2001 | printer | 500000
    2004 | hard drive | 8000000
    2002 | printer | 5460000
    2005 | hard drive | 500000...
    ................ ..... . . .
    *****************************************
    etc.....

    i want to see best 3 item sold in 2001,best 3 item sold in 2002 and so on in a single query
    please help me for this

    Thanks
    Bijendra

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    As you have not posted the DDL for the table and sample data as INSERT, there is nothing we can play around with.
    So this is just a shot in the dark, but something like the following will probably do it.
    Code:
    SELECT *
    FROM (
      SELECT years, 
             item_name, 
             quantity_sold,
             rank() over (partition by year order by quantity_sold) as sold_rank
      FROM the_table_with_no_name
    ) t
    WHERE sold_rank <= 3;

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    Thanks Shammat for this sql query and have a nice day.

    Bijendra

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •