CODEWITHSUNDEEP
sqlsql-serverdatabase
mcedave
mcedave

Reputation: 15

Return distinct records with the shortest length - SQL

I have a table with Attributes, Definitions and Tokens. Tokens in this case is an abbreviation used to represent the Definition. Multiple people enter the information so the tokens are not consistent. I want to find the smallest length token for each Attribute. Examples are:

Attribute Definition Token TokenLength
Product Type Adapter ADAPTER 7
Product Type Adapter ADAPTR 6
Product Type Adapter ADP 3
Product Type Adapter ADPT 4
Product Type Adapter ADPTR 5
Feature Automatic AUTO 4
Feature Automatic AUTO-VENT 9
Feature Automatic AUTOMATIC 9
Collection/Series Classic Rib CL-RIB 6
Collection/Series Classic Rib CLASSIC_R 9
Collection/Series Classic Rib CLASSIC_RIB 11
Collection/Series Classic Rib CR 2

I want to results to be:

Attribute Definition Token TokenLength
Product Type Adapter ADP 3
Feature Automatic AUTO 4
Collection/Series Classic Rib CR 2

I have tried variations of LEN and MIN among others and I cannot seem to get what I am looking for. This statement has gotten me the closest, but not quite.

SELECT Attribute, Definition,Token, len(Token)
FROM TableName
WHERE LEN(Token) = (SELECT MIN(LEN(Token)) FROM TableName) 
ORDER BY Token

Upvotes: 0

Views: 64

Answers (4)

Popeye
Popeye

Reputation: 35920

You can use various methods for it. One of the method is to use NOT EXISTS as follows:

Select t.* from your_table t
 Where not exists
       (Select 1 from your_table tt
         Where t.Attribute = tt.Attribute And len(tt.token) < len(t.token) )

Upvotes: 1

Charlieface
Charlieface

Reputation: 72060

As well as a correlated subquery, you can also do this using a window function:

SELECT Attribute, Definition, Token, TokenLength
FROM (
    SELECT *,
        TokenLength = LEN(Token)
        rn = ROW_NUMBER() OVER (PARTITION BY Attribute ORDER BY LEN(Token))
    FROM TableName a
) a
WHERE rn = 1
ORDER BY Token

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

You can use ROW_NUMBER() (or RANK() if you want duplicates). For example:

select *
from (
  select *, 
    row_number() over(partition by attribute order by tokenlength) as rn
  from t
) x
where rn = 1

Upvotes: 1

Bohemian
Bohemian

Reputation: 425208

Correlate the subquery:

SELECT Attribute, Definition, Token, len(Token)
FROM TableName t
WHERE LEN(Token) = (
    SELECT MIN(LEN(Token))
    FROM TableName a
    WHERE a.Attribute = t.Attribute
) 
ORDER BY Token

Upvotes: 1

Related Questions