February 20, 2014 at 10:11 pm
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,
February 20, 2014 at 10:56 pm
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
February 20, 2014 at 11:06 pm
Thanks for the reply ..
the condition is the query should be within 140 characters..
Rajesh.
February 20, 2014 at 11:56 pm
remove all the (nolock) hints.
February 21, 2014 at 12:06 am
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
February 21, 2014 at 1:00 am
Homework or competition? What have you tried so far?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 21, 2014 at 1:24 am
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
February 21, 2014 at 1:26 am
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.
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
February 21, 2014 at 2:22 am
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