SQL Query

  • SalesOrder

    SalesOrderIDItemIDSalesAmount

    1101100

    1104120

    2101200

    2103225

    3101200

    3102240

    3103450

    4101100

    4102480

    4103450

    4104560

    5105666

    ItemDetail

    ItemIDCatID

    1011

    1022

    1031

    1042

    1052

    “SalesOrder” table has order details and “ItemDetail” table has items detail. A select query is required to get all those item’s amount from SalesOrder table whose amount is greater than category 1 item’s (101,103) amount

    Expected Result

    SalesOrderIDItemIDSalesAmount

    4102480

    4104560

    5105666

    No hardcoding is allowed. TOP, IN, MAX and Except keywords are not allowed in query.

    Thanks,

  • create table dbo.SalesOrder(SalesOrderID int ,ItemID int ,SalesAmount int)

    insert into dbo.SalesOrder(SalesOrderID,ItemID,SalesAmount)

    select 1,101,100

    union all

    select 1,104,120

    union all

    select 2,101,200

    union all

    select 2,103,225

    union all

    select 3,101,200

    union all

    select 3,102,240

    union all

    select 3,103,450

    union all

    select 4,101,100

    union all

    select 4,102,480

    union all

    select 4,103,450

    union all

    select 4,104,560

    union all

    select 5,105,666

    create table dbo.ItemDetail(ItemID int ,CatID int)

    insert into dbo.ItemDetail(ItemID,CatID)

    select 101,1

    union all

    select 102,2

    union all

    select 103,1

    union all

    select 104,2

    union all

    select 105,2

    --Sql

    with CTE

    as(

    select Id.itemid,SalesAmount,row_number() over(order by SalesAmount desc) as RN

    from dbo.SalesOrder so (nolock)

    join dbo.ItemDetail Id (nolock)

    on so.itemid=Id.itemid

    where id.CatID=1)

    select so1.* from CTE a (nolock)

    join dbo.SalesOrder so1 (nolock)

    on so1.SalesAmount>a.SalesAmount

    where RN=1

  • Thanks for the reply ..

    the condition is the query should be within 140 characters..

    Rajesh.

  • remove all the (nolock) hints.

  • Even though i am using the below code it is 235 chars( from the cte query it is 280 chars)

    select s1.* from(

    select s.*,row_number() over(order by SalesAmount desc) R

    from dbo.SalesOrder s join dbo.ItemDetail I

    on s.itemid=I.itemid

    where I.CatID=1) a join dbo.SalesOrder s1

    on s1.SalesAmount>a.SalesAmount

    where R=1

    Pls. help

  • Homework or competition? What have you tried so far?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • If you can't use MAX, use MIN 🙂

    select -MIN(-SalesAmount)

    from SalesOrder s

    join ItemDetail i on i.ItemID = s.ItemID and i.CatID = 1

  • nrajeshkumar (2/20/2014)


    the condition is the query should be within 140 characters..

    Please link the site to the competition so we can see all the rules at once instead of you feeding them to us piecemeal, or let us know which chapter in the book you're on so we can help you find the correct solution to what your teachers need.

    These limitations are no longer in the realm of business needs, but are esoteric design limitations, only imposed for the particular reasons of teaching or mechanism exploration.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • nrajeshkumar (2/20/2014)


    Thanks for the reply ..

    the condition is the query should be within 140 characters..

    Rajesh.

    Does it need to be red and flowery or green and stripy?

Viewing 9 posts - 1 through 8 (of 8 total)

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