CODEWITHSUNDEEP
t-sqlsubstringdistinct
SupremeDud
SupremeDud

Reputation: 1411

Selecting distinct substring values

I have a field that is similar to a MAC address in that the first part is a group ID and the second part is a serial number. My field is alphanumeric and 5 digits in length, and the first 3 are the group ID.

I need a query that gives me all distinct group IDs and the first serial number lexicographically. Here is sample data:

ID
-----
X4MCC
X4MEE
X4MFF
V21DD
8Z6BB
8Z6FF

Desired Output:

ID
-----
X4MCC
V21DD
8Z6BB

I know I can do SELECT DISTINCT SUBSTRING(ID, 1, 3) but I don't know how to get the first one lexicographically.

Upvotes: 2

Views: 4750

Answers (2)

gbn
gbn

Reputation: 432271

SELECT
   ID
FROM
   (
   SELECT
      ID,
      ROW_NUMBER() OVER (PARTITION BY SUBSTRING(ID, 1, 3) ORDER BY ID) AS rn
   FROM MyTable
   ) oops
WHERE
   rn = 1

Upvotes: 2

a1ex07
a1ex07

Reputation: 37364

Another way which seems to have the same cost as the query by gbn:

SELECT MIN(id)
FROM your_table
GROUP BY SUBSTRING(id, 1, 3);

Upvotes: 3

Related Questions