Use Top with IN - 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)

Use Top with IN

Author

Topic

a_Qaddomi
Starting Member

6 Posts

Posted - 2011-12-24 : 20:55:55

I have table named ComputerParts contains PartID as a PrimaryKey and PartName, and I have another table called PartTypes contains PartTypeID as a PrimaryKey ,PartTypeName ,Price and PartID as a F.K .

for Example
PartID PartName
------ --------
1 ______ MotherBoard
2 ______ CPU
3 ______ HardDisk

PartTypeID PartTypeName Price PartID
---------- ------------ ---- -----
1 ______ i7______ 200______ 2
2______ i5 ______ 150______ 2
3 ______ SilverStone______ 50______ 1
4 ______ Apevia______ 70______ 1
5 ______ Seagate500______ 60______ 3
6 ______ Seagate800 ______ 80 ______ 3

I need a select statement that get 1 parttype from each of three categories with total price less than 300 dollar, I tried to use Top with in like this :

select top 3 PartID,PartTypeName from dbo.PartType where PartID in (1,2,3) and Price <300 order by PartID,PartTypeName desc

Please i need your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-24 : 21:18:25

[code]
select PartID,PartTypeName
from
(
select ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY PartTypeID) AS Rn,
PartID,
PartTypeName
from dbo.PartType
WHERE Price< 300
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

a_Qaddomi
Starting Member

6 Posts

Posted - 2011-12-25 : 15:20:02

I need top 1 of each of them with total price under 300 ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-25 : 23:20:24

when you say top 1 you need to explain us on what basis you want top 1 to be considered. There's no concept of ordering in sql table unless you specify it by means of another field using ORDER BY clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources