SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query

Author

Topic

nrajeshkumars
Starting Member

5 Posts

Posted - 2014-02-20 : 23:34:09

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

169 Posts

Posted - 2014-02-21 : 00:02:24

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

nrajeshkumars
Starting Member

5 Posts

Posted - 2014-02-21 : 00:08:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources