Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Return a single row when more than two columns have the same value

I need to return just one row when I have two or more equals rows with same values in two specifics columns. The select row must be the latest in column DATE.

SELECT * FROM `history` WHERE productId IN (166, 2915) ORDER BY history.value;

OBS: For productId, I can have more than 20 items. It is just only example.

ACTUAL RETURN

id supermarketId productId value date
2722 2 2915 7.98 2023-11-19 00:00:00
180 3 166 7.99 2023-07-23 00:00:00
1639 2 166 8.98 2023-06-05 00:00:00
4251 2 166 8.98 2024-02-17 00:00:00
4220 2 2915 8.98 2024-02-17 00:00:00
2759 3 2915 9.99 2023-11-15 00:00:00

DESIRED RETURN

id supermarketId productId value date
180 3 166 7.99 2023-07-23 00:00:00
4251 2 166 8.98 2024-02-17 00:00:00
4220 2 2915 8.98 2024-02-17 00:00:00
2759 3 2915 9.99 2023-11-15 00:00:00

I tried:

SELECT * FROM `history` WHERE productId IN (166, 2915) 
AND date = (SELECT MAX(date) FROM history) ORDER BY history.value;

But some rows were eliminated:

id supermarketId productId value date
4251 2 166 8.98 2024-02-17 00:00:00
4220 2 2915 8.98 2024-02-17 00:00:00
Tags:

2

Answers


  1. 0

    If I understand the question, then following query should solve the problem

    SELECT A.* FROM [history] A
INNER JOIN (SELECT [productId], supermarketId, MAX(date) as date
            FROM [history]
            GROUP BY [productId], supermarketId
    
            HAVING COUNT(*) >= 1 and productId in (2915, 166)) as B
ON A.[productId] = B.[productId] AND A.supermarketId = B.supermarketId and A.date = b.date
    Login or Signup to reply.
  2. 0

    first,check your id format,then use between 166 and 2915

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search