CODEWITHSUNDEEP
sqlsql-server
Charles
Charles

Reputation: 5

Comparing data from the same column in SQL Server

I have a table that has a column with data like below:

aaaa;1
aaaa;2
aaaa;3
bbbb;1
cccc;1
dddd;1
dddd;2

I need to select the data with the highest number after the semicolon (;) like this:

aaaa;3
bbbb;1
cccc;1
dddd;2

Can anyone give me ideas to how to do this?

Upvotes: 0

Views: 428

Answers (4)

Onur Cete
Onur Cete

Reputation: 273

you can use rank() like that;


    select column_name
      from (select a.*,
                   rank() over(partition by substr(column_name, 1, 4) order by substr(column, 6) desc) as row_num
              from table_name a)
     where row_num = '1';

Upvotes: 0

Eric
Eric

Reputation: 5733

As simple as this:

select 
    -- Construct the string by left part + max(right part)
    LEFT([column], CHARINDEX(';', [column], 0) - 1) + ';' + 
    MAX(RIGHT([column], LEN([column]) - CHARINDEX(';', [column], 0)))
from 
    [table]
group by 
    LEFT([column], CHARINDEX(';', [column], 0) - 1) -- The left part of ';'

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use LEFT, SUBSTRING and CHARINDEX functions to achieve this. Read this article for more information.

SQL Fiddle

WITH CteSplit(string, leftString, rightString, RN) AS(
    SELECT 
        string,
        LEFT(string, CHARINDEX(';', string, 0) - 1),
        CAST(SUBSTRING(string, CHARINDEX(';', string, 0) + 1, LEN(string) - CHARINDEX(';', string, 0)) AS INT),
        RN = ROW_NUMBER() OVER(
                PARTITION BY 
                    LEFT(string, CHARINDEX(';', string, 0) - 1)
                ORDER BY
                    CAST(SUBSTRING(string, CHARINDEX(';', string, 0) + 1, LEN(string) - CHARINDEX(';', string, 0)) AS INT) DESC
            )
    FROM TestTable
)
SELECT
    string
FROM CteSplit
WHERE RN = 1

Upvotes: 0

Aeroradish
Aeroradish

Reputation: 371

Use CharIndex to discover the location of the semicolon and then sort using the result. Example code below:

declare @table table (
col1 varchar(25)
)

insert into @table (col1) values ('aaaa;1')
insert into @table (col1) values ('aaaa;2')
insert into @table (col1) values ('aaaa;3')
insert into @table (col1) values ('bbbb;1')
insert into @table (col1) values ('dddd;1')
insert into @table (col1) values ('dddd;2')

select top 1
col1,
charindex(';',col1,0) as SemiColonLocation,
substring(col1, 0, charindex(';',col1,0) + 1) as TextVal,
substring(col1, charindex(';',col1,0) + 1, (len(col1) - charindex(';',col1,0))) as AfterVal
from @table
order by substring(col1, charindex(';',col1,0) + 1, (len(col1) - charindex(';',col1,0))) desc

Upvotes: 0

Related Questions