SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nrajeshkumars
Starting Member

India
5 Posts

Posted - 02/20/2014 :  23:34:09  Show Profile  Reply with Quote
SalesOrder

SalesOrderID ItemID SalesAmount
1 101 100
1 104 120
2 101 200
2 103 225
3 101 200
3 102 240
3 103 450
4 101 100
4 102 480
4 103 450
4 104 560
5 105 666

ItemDetail

ItemID CatID
101 1
102 2
103 1
104 2
105 2

“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

SalesOrderID ItemID SalesAmount
4 102 480
4 104 560
5 105 666

Pls. help me in writing query to get the expected result.

Thanks,
Rajesh.

N.Rajesh kumar

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 02/21/2014 :  00:02:24  Show Profile  Reply with Quote
SELECT SalesOrderId,S.ItemId,S.SalesAmount FROM
(SELECT S.ItemId,max(S.SalesAmount)AS SalesAmount FROM @SalesOrder AS S
INNER JOIN @ItemDetail AS I
ON S.ItemID = I.ItemID
WHERE I.CatId > 1
GROUP BY S.ItemId)x
INNER JOIN @SalesOrder S
ON X.SalesAmount = S.SalesAmount


Veera
Go to Top of Page

nrajeshkumars
Starting Member

India
5 Posts

Posted - 02/21/2014 :  00:08:13  Show Profile  Reply with Quote
Veera,

Thanks for the post.

but the condition is

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

N.Rajesh kumar

Edited by - nrajeshkumars on 02/21/2014 00:46:27
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000