Walter Nuñez Walter Nuñez - 7 months ago 13
SQL Question

Mysql PHP Select with multiple values, MAX(LENGTH())

hello friends i am working on a query to get a list of registry, but i need get duplicate entries, and preserve only that have more length

Example table:

ID = INT Auto increment
IDref = INT not null
UserId = INT ZeroFill

+--------+---------+--------+---------+
| ID | IDref | UserId | |
+--------+---------+--------+---------+
| 1 | 1 | 00001 | |
| 3 | 1 | 001 | |
| 4 | 1 | 002 | |
| 5 | 2 | 00001 | |
| 6 | 2 | 001 | |
| 7 | 2 | 002 | |
| 8 | 3 | 00001 | |
| 9 | 4 | 002 | |
+--------+---------+--------+---------+


The Query

SELECT ID, IDref, UserId
FROM Table
WHERE UserId = '00001' OR '002'
GROUP BY IDref
HAVING count(IDref) > 0
ORDER BY CHAR_LENGTH(UserId ) DESC
LIMIT 1


Update
I am Geting

+--------+---------+--------+---------+
| ID | IDref | UserId | |
+--------+---------+--------+---------+
| 4 | 1 | 002 | |
+--------+---------+--------+---------+


i required this output:

+--------+---------+--------+---------+
| ID | IDref | UserId | |
+--------+---------+--------+---------+
| 1 | 1 | 00001 | |
| 5 | 2 | 00001 | |
| 8 | 3 | 00001 | |
| 9 | 4 | 002 | |//count(IDref) > 0// one registry find.
+--------+---------+--------+---------+


But not work my Query. someone can help me plz.

Last Update with working code Thank Barmar:

SELECT t1.ID, t1.IDref, t1.UserId
FROM Table1 AS t1
JOIN (SELECT IDref, MAX(LENGTH(UserId)) AS maxlength
FROM Table1
WHERE UserID IN ('00001', '002')
GROUP BY IDref) AS t2
ON t1.IDref = t2.IDref AND LENGTH(t1.UserId) = t2.maxlength
WHERE t1.UserId IN ('00001', '002')


ok , but may you see the performance of this query in a table with 20,000 records will be faster?

Answer
WHERE UserId = '00001' OR '002'

is not the correct way to test if a column matches either of these values. The correct way is

WHERE UserId IN ('00001', '002')

Your code is equivalent to

WHERE (UserId = '00001') OR '002'

which is always true because '002' is not false.

You also should remove LIMIT 1. Otherwise, you only get one of the IDRef that has duplicates.

ORDER BY LENGTH(UserId) doesn't make the grouped value select the row with the longest length -- ordering is done after grouping, and grouping just selects one of the values arbitrarily. For that, you need to use one of the techniques in SQL Select only rows with Max Value on a Column

SELECT t1.ID, t1.IDref, t1.UserId
FROM Table1 AS t1
JOIN (SELECT IDref, MAX(LENGTH(UserId)) AS maxlength
      FROM Table1
      WHERE UserID IN ('00001', '002')
      GROUP BY IDref) AS t2
ON t1.IDref = t2.IDref AND LENGTH(t1.UserId) = t2.maxlength
WHERE t1.UserId IN ('00001', '002')

There's no need for HAVING COUNT(IDRef) > 0, because count is always more than 0.

DEMO