Menu
I have a table mytable like below;
╔═════════╦══════╦═════╗
║ product ║ tag ║ lot ║
╠═════════╬══════╬═════╣
║ 1111 ║ 101 ║ 2 ║
║ 1111 ║ 102 ║ 5 ║
║ 2222 ║ 103 ║ 6 ║
║ 3333 ║ 104 ║ 2 ║
║ 4444 ║ 101 ║ 2 ║
║ 5555 ║ 101 ║ 2 ║
║ 5555 ║ 102 ║ 5 ║
║ 6666 ║ 102 ║ 2 ║
║ 6666 ║ 103 ║ 5 ║
║ 7777 ║ 101 ║ 2 ║
║ 7777 ║ 102 ║ 5 ║
║ 7777 ║ 103 ║ 6 ║
║ 8888 ║ 101 ║ 1 ║
║ 8888 ║ 102 ║ 3 ║
║ 8888 ║ 103 ║ 5 ║
║ 9999 ║ 101 ║ 6 ║
║ 9999 ║ 102 ║ 8 ║
╚═════════╩══════╩═════╝
I have the input 101,102. I want the output like;
2,5
6,8
I have a query like;
select group_concat(lot order by lot)
from `mytable`
group by product
having group_concat(tag order by tag) = '101,102';
It returns;
2,5
2,5
6,8
Instead of two 2,5, I want just one, avoiding duplicate rows. How can I do this?
Here is the fiddle http://sqlfiddle.com/#!9/7a78bb/1/0
583
If you do not include DISTINCT in a SELECT clause, you might find duplicate rows in your result, because SQL returns the JOB column's value for each row that satisfies the search condition. Null values are treated as duplicate rows for DISTINCT.
The SQL DISTINCT keyword, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
SQL Delete Duplicate Rows using Group By and Having Clause According to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns.
If you want distinct then
select distinct group_concat(lot order by lot)
from `mytable`
group by product
having group_concat(tag order by tag) = '101,102';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
