select only when different value

Question

I have this column: name and price. I don't really know how or why in mysql database there are few line that are double record exactly from the previous line.

how to select all records but show only one of the records if the record is double with a line in front or behind it?

For example I have this records:

id	name	price
1	book	5
2	lamp	7
3	lamp	7
4	book	5
5	book	5

the result I want is:

id	name	price
1	book	5
2	lamp	7
4	book	5

More information required, please add your code , table definition, sample data , current output and expected output as text to the question. As things stand duplicate output is as it should be. — P.Salmon, Mar 08 '21 at 07:43
Apart from the id in your result name and price are the same for book - I am a bit baffled why you would want this. — P.Salmon, Mar 08 '21 at 08:35

score 0 · Answer 1 · answered Mar 08 '21 at 10:32

0

Select distinct is not an option here as id column is always unique. I guess this will work for you:

select min(id), name, price from table_name group by name, price

answered Mar 08 '21 at 10:32

Igor Pigin

81
7

almost, but with this query it only get id 1 and 2. I need the id 4 also – Faiz Mar 08 '21 at 11:07

score 0 · Accepted Answer · answered Mar 08 '21 at 11:01

If you want to exclude rows that match the previous name, there are several ways like the following.

Case 1: If you use MySQL8, you can use the LAG function.

SELECT t1.id,t1.name,t1.price FROM (
  SELECT t2.id,t2.name,t2.price,
    LAG(t2.name) OVER(ORDER BY t2.id) prev
  FROM mytable t2
) t1
WHERE t1.prev IS NULL OR t1.name<>t1.prev
ORDER BY 1

Case 2: If the ids are continuous without any steps, you will get the expected result by comparing name and the previous id by JOIN.

SELECT t1.id,t1.name,t1.price FROM mytable t1
  LEFT JOIN mytable t2
  ON t1.name=t2.name AND
     t1.id=t2.id-1
WHERE t1.id=1 OR t2.id IS NOT NULL
ORDER BY 1

Case 3: If the ids are not continuous, there is a way to get the maximum id that does not exceed the other id.

SELECT t1.id,t1.name,t1.price FROM mytable t1
  LEFT JOIN mytable t2
  ON t1.name=t2.name AND
     t1.id=(SELECT MAX(t3.id) FROM mytable t3 WHERE t3.id<t2.id)
WHERE t1.id=1 OR t2.id IS NOT NULL
ORDER BY 1

DB Fiddle

could you please see this https://stackoverflow.com/questions/66729924/mysql-remove-duplicate-data-if-sequential — Faiz, Mar 21 '21 at 07:44

select only when different value

2 Answers2

Linked