Show Distinct Result Column of Substring in SQL-CodePudding

I am trying to extract the prefix (e.g. 12- of 12-111111) of some file names into a column. Then found that there are many duplicated rows in the column. How to show the distinct values in the column? I tried Group By, but it does not work. Thank you.

SELECT TOP 100
    b.filename, 
    SUBSTRING( b.filename, CHARINDEX( '-', filename )   1, LEN( filename ) ) AS [prefix] 
FROM
    dbo.share AS b

Tried to use GROUP BY:

SELECT 
    b.filename, 
    SUBSTRING( b.filename, 0, CHARINDEX( '-', filename )   1 ) AS [prefix] 
FROM
    dbo.share AS b
GROUP BY
    [prefix]

SELECT 
    b.filename, 
    SUBSTRING( b.filename, 0, CHARINDEX( '-', filename )   1 ) AS [prefix] 
FROM
    dbo.share AS b
GROUP BY
    SUBSTRING( b.filename, 0, CHARINDEX( '-', filename )   1 )

CodePudding user response：

Try something like this

WITH CTE AS(

SELECT 
    b.filename, 
    SUBSTRING( b.filename, 0, CHARINDEX( '-', filename )   1 ) AS [prefix],
    ROW_NUMBER() OVER(PARTITION BY SUBSTRING( b.filename, 0, CHARINDEX( '-', filename )   1 )
       ORDER BY b.filename DESC) AS RowNumber 
FROM
    dbo.share AS b

)
SELECT filename, prefix
FROM CTE
WHERE RowNumber = 1