Find distinct values in SQL when subquery returns more than one row?

Question

These are my tables

Table1

• price
• city_category_id
• city_product_id

Here are three rows"

price | city_category_id | city_product_id
------+------------------+--------------------------
1500  | CHDELCLAPTOPDELL | CHDELCLAPTOPDELLVOSTR8
1200  | CHDELCLAPTOPDELL | CHDELCLAPTOPDELLVOSTR816
1000  | CHDELCLAPTOPDELL | CHDELCLAPTOPDELLVOSTR816

Here I have to find firstly distinct product_name and then select min price of the distinct elements.I want output as CHDELCLAPTOPDELLVOSTR816 and 1200 and CHDELCLAPTOPDELLVOSTR816 and 1000.

QUERY

select min(price) 
from sellers_product 
where city_product_id=
(
   select distinct city_product_id 
   from sellers_product 
   where city_category_id='CHDELCLAPTOPDELL'
)

ERROR

I know why this error is coming because there are more than 1 rows returned by subquery but is there any way to get the desired output using only 1 query.

Answer 1

As I understand you want something like this:

DECLARE @sellers_product TABLE (price INT, Category_id VARCHAR(100), product_name VARCHAR(100))


INSERT INTO @sellers_product SELECT 1500, 'DELL', 'Vostro123'
INSERT INTO @sellers_product SELECT 1200, 'DELL', 'Vostro1234'
INSERT INTO @sellers_product SELECT 1000, 'DELL', 'Vostro123'



SELECT product_name, MIN(price) AS minPrice
FROM @sellers_product
WHERE Category_id = 'DELL'
GROUP BY product_name

Results:

product_name    minPrice
Vostro123       1000
Vostro1234      1200

This select first filters by Category_id for rows/categories, which you need, and then groups by 'product_name' to get unique name. In this case we group 2 rows with 'Vostro123' product_name. From this grouped rows we can also take MIN price value.

Answer 2

Try this and let me know if it is working. If your subquery returns more than one row then you cant use = as it will return single value:

select distinct city_product_id,min(price),product_name from sellers_product 
    where city_category_id='CHDELCLAPTOPDELL'
 Group By product_name

Answer 3

;WITH CTE_RESULT AS 
(
    SELECT price, city_category_id, city_product_id, -- USE THE SELECT COLUMNS HERE
    ROW_NUMBER() OVER (PARTITION BY city_product_id --USE THE DISTINCT COLUMN HERE 
    ORDER BY price ASC -- CHANGE THE ORDER HERE) ROW_NO
    FROM #TABLE1
)

SELECT price, city_category_id, city_product_id FROM CTE_RESULT 
WHERE ROW_NO=1 -- FILTERS THE DUPLICATE ROWS

Answer 4

here is my answer using sqlite version 3 with your new data

.mode column
.separator ","
.header ON
.width 25 25

DROP TABLE IF EXISTS sellers_product;

CREATE TABLE sellers_product (
    price             INT,
    city_category_id  VARCHAR(100),
    city_product_id   VARCHAR(100));

INSERT INTO sellers_product VALUES(1500, 'CHDELCLAPTOPDELL', 'CHDELCLAPTOPDELLVOSTR8');
INSERT INTO sellers_product VALUES(1200, 'CHDELCLAPTOPDELL', 'CHDELCLAPTOPDELLVOSTR816');
INSERT INTO sellers_product VALUES(1000, 'CHDELCLAPTOPDELL', 'CHDELCLAPTOPDELLVOSTR816');


SELECT city_product_id, MIN(price) AS minPrice
    FROM  sellers_product
    WHERE city_category_id = 'CHDELCLAPTOPDELL'
    GROUP BY city_product_id;


------------- output -----------------

city_product_id            minPrice
-------------------------  -------------------------
CHDELCLAPTOPDELLVOSTR8     1500
CHDELCLAPTOPDELLVOSTR816   1000