CODEWITHSUNDEEP
mysql
Saroj Shrestha
Saroj Shrestha

Reputation: 2875

Get distinct column value from a specific value ignoring value from other retrieved column - mysql

I am running a query:

SELECT DISTINCT(length(unit_number)) as strlen, unit_number FROM `units` where building_id > 783 and building_id < 793

and it returns data like:

strlen | unit_number
    6  |  A.1001
    6  |  A.1002
    6  |  A.1003
    7  |  A.10001
    7  |  A.10002
    8  |  A.100001
    8  |  A.100002

However, I don't want this strlen column to have a duplicate value. I want something like:

strlen | unit_number
    6  |  A.1001
    7  |  A.10001
    8  |  A.100001

I don't mind if it picks the first row from their type or from last. I just want to make sure that strlen column has unique value.

Upvotes: 0

Views: 21

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY LENGTH(unit_number) ORDER BY unit_number) rn
    FROM yourTable
)

SELECT LENGTH(unit_number) AS strlen, unit_number
FROM cte
WHERE rn = 1;

On earlier versions of MySQL which do not support ROW_NUMBER, we can try:

SELECT distinct LENGTH(t1.unit_number) AS strlen, t1.unit_number
FROM units t1
INNER JOIN
(
    SELECT LENGTH(unit_number) AS strlen, MIN(unit_number) AS min_unit_number
    FROM units where building_id > 783 and building_id < 793
    GROUP BY strlen
) t2
    ON LENGTH(t1.unit_number) = t2.strlen AND t1.unit_number = t2.min_unit_number;

Upvotes: 1

Related Questions