Mysql – Finding the same value in a column and only returning rows without the same value

database-designMySQL

I have a query that i have to find the DiscountPercent that are the same and not show the rows with the same DiscountPercent. I have tryd this and it brings back the values that are the same

SELECT
    ProductName,DiscountPercent
FROM
    Products 
WHERE
    DiscountPercent in (SELECT DiscountPercent
                        FROM Products
                        GROUP BY DiscountPercent
                        HAVING COUNT(*) < 1)
ORDER BY
    ProductName;

What i want to accomplish is to bring in the products that don't have the same discount percentage. I don't think i am doing this the right way.What is the correct way of doing this?

This is some data in the ProductName and in the DiscountPercentage

ProductName        DiscountPercent
Fender Precision     30.00
Fender Stractocaster 30.00
Gibson Les Paul      30.00
Gibson SG            52.00
Hofner Icon          25.00
Ludwig 5-piece       30.00
Rodriguez Cabelle    39.00
Tama 5-piece         15.00
Washburn D10         0.00
Yamaha FG700s        38.00

The Desired result that must use a subquery

ProductName       DiscountPercent
Fender Precision    30.00
Gibson SG           52.00
Hofner Icon         25.00
Rodrgiguez Cabelle  39.00
Tama 5-piece        15.00
Washiburn D10       0.00
Yamaha FG700s       38.00

Best Answer

SELECT MIN(ProductName), DiscountPercent
   FROM Products 
   GROUP BY DiscountPercent;

You might find the output from this interesting:

SELECT DiscountPercent,
       GROUP_CONCAT(ProductName)
   FROM Products 
   GROUP BY DiscountPercent;

PROPOSED SOLUTION

SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
    name,
    (@nametag:=MD5(CONCAT(name,':',result))),
    (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    (@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;

PROPOSED SOLUTION EXECUTED

mysql> SELECT name,consecutive FROM
    -> (SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id) A
    -> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle |           3 |
+------+-------------+
1 row in set (0.02 sec)

mysql>

SUBQUERY'S OUTPUT

mysql> SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc                              |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e         |           1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0         |           1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

I just answered a similar question 2 days ago (Logic Value of Assignment Expression)

Best Answer

Related Solutions

Mysql – How to remove multiple records from one table based on the non-existence of a record in another table

MySQL – Counting Consecutive Rows with Same Value Ordered by Different Field

PROPOSED SOLUTION

PROPOSED SOLUTION EXECUTED

SUBQUERY'S OUTPUT

GIVE IT A TRY !!!

Related Question