0

I'm trying to make a query where it doesn't include products that have the same discount percent as another product. I'm also suppose to use a subquery. This is what I've came up with but I'm getting an error saying, "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

SELECT ProductName,
 (SELECT DiscountPercent
 FROM Products
 GROUP BY DiscountPercent
 HAVING COUNT(DiscountPercent) = 1
 )
FROM Products

Fender Stratocaster 30.00
Gibson Les Paul 30.00
Gibson SG   52.00
Yamaha FG700S   38.00
Washburn D10S   0.00
Rodriguez Caballero 11  39.00
Fender Precision    30.00  
Hofner Icon 25.00
Ludwig 5-piece Drum Set with Cymbals    30.00
Tama 5-Piece Drum Set with Cymbals  15.00
jaramore
  • 389
  • 1
  • 3
  • 12
  • SELECT ProductName, (SELECT DiscountPercent FROM Products P1 where p1.ProductName=p.ProductName GROUP BY DiscountPercent HAVING COUNT(DiscountPercent) = 1 ) FROM Products P – KumarHarsh Feb 19 '14 at 06:45
  • It's not right. I'm still getting 3 of the same values on 3 products – jaramore Feb 19 '14 at 06:47
  • provide sample data to get accurate result. select * from (select *,row_number()over partition by(ProductName,DiscountPercent order by ProductName)rn from Products)t4 where rn=1 – KumarHarsh Feb 19 '14 at 06:50
  • I provided the sample data above – jaramore Feb 19 '14 at 06:57
  • what version of sql server are you using? if it's sql server 2012, you have the possibility in a query to check the previous row returned for example : min(discount) OVER (ORDER BY [discount] ROWS UNBOUNDED PRECEDING. Check on internet for more detail. If you have a 2005+ and not too many rows you should investigate in recurring query – Mathese F Feb 19 '14 at 07:05
  • Just so I understand you correctly: You want a result where every Discount exists excatly once and it does not matter which product it is, if the discount exists more then once? – Marco Feb 19 '14 at 07:10
  • I'm using SQL server 2012. Yes I need only unique values for the DiscountPercent. – jaramore Feb 19 '14 at 07:25

2 Answers2

0

try this,

select * from (
select *,row_number()over (partition by Discount order by ProductName)rn from @t
)t4 where rn=1
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
0
SELECT 
[ProductName],
[DiscountPercent]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY DiscountPercent ORDER BY ProductName) AS [ProductRank],
[ProductName],
[DiscountPercent]
FROM
[Products]
) AS Data
WHERE [ProductRank] = 1
Abhishek Chaudhary
  • 767
  • 6
  • 6