Menu
This might not be very sensible, but I'ld like to let MySQL return me the exact duplicate rows if there are duplicate criteria in the WHERE IN clause. Is this possible?
Take this example:
SELECT
columns
FROM
table
WHERE
id IN( 1, 2, 3, 4, 5, 1, 2, 5, 5)
I'ld like MySQL to return me rows with id 5 three times, id's 1 and 2 twice, and 3 and 4 once.
As the lenght of the IN arguments, as well as the duplicate count (once, twice, three times, etc.), will be arbitrary I don't want to rely on UNION or JOIN. Is something like this possible otherwise?
963
The SQL UNION ALL operator does not remove duplicates. If you wish to remove duplicates, try using the UNION operator.
Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.
Let us take an example – The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name. CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );
By default, duplicate rows are removed from UNION results. The optional DISTINCT keyword has the same effect but makes it explicit. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.
I'm not sure why you want to ban JOIN as its fairly essential to SQL. It's like banning function calls in a functional language.
A good way to solve this is to create a result set containing the ids you want to return and join with it. Here's one way to do it:
SELECT Table1.*
FROM Table1
JOIN (SELECT 1 AS id
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 5) AS T1
ON Table1.id = T1.id
I'm not sure if you have considered this method? It has none of the problems that you seem to be afraid of.
If you ban joins you can't do this unless you use a stored procedure, which I'd say is worse than joining.
110
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
