CODEWITHSUNDEEP
sql-server
nrvbha
nrvbha

Reputation: 137

How to select one row from similar records in sql server?

I have a table which contains data like this, 

GroupName   group_code  company_name   Company      Jan    Feb  
  USA         G142       3M Corp        3MCorp      117     35
  USA        G23581      3M Corp        3MCorp      117     35
  USA         G899       3M Corp        3MCorp      117     35
  USA         G2165      MNA Inc.       MNA Inc.     5       3
  USA         G6245      MNA Inc.       MNA Inc.     5       3
  USA         G3425     Acterna Inc.  Acterna Inc.  245     204

Final result should be 1 row per company name with maximum group_code value like this,

GroupName   group_code  company_name   Company      Jan    Feb  
  USA        G23581      3M Corp        3MCorp      117     35
  USA         G6245      MNA Inc.       MNA Inc.     5       3
  USA         G3425     Acterna Inc.  Acterna Inc.  245     204

How can I do that?

Upvotes: 0

Views: 40

Answers (4)

Dan
Dan

Reputation: 5231

This should work for the test data you provided. It looks like you want to take the group_code that has the largest value (numerically) trailing the leading character. So, if I assume that correctly, you could try this:

WITH cteRankedGroupCodes
AS
(
    select  GroupName
            , group_code
            , company_name
            , company
            , jan
            , feb
            , rank() over ( partition by company_name 
                            order by convert(int, substring(group_code, 2, len(group_code))) desc
                           ) as GroupCodeRank
    from SimilarRecords --your table
)

select  GroupName
        , group_code
        , company
        , jan
        , feb 
from cteRankedGroupCodes 
where GroupCodeRank = 1

Upvotes: 1

Dudi Konfino
Dudi Konfino

Reputation: 1136

select GroupName ,  group_code , company_name  , Company   ,  Jan   ,Feb  
from table
group by GroupName   ,  company_name   ,Company   ,   Jan   , Feb 
having   group_code = max(group_code)

Upvotes: 0

Pekka
Pekka

Reputation: 3644

Use

SELECT [GroupName], max([group_code]), [company_name], [Company], [Jan], [Feb]
    FROM Table1
    GROUP BY [GroupName], [company_name], [Company], [Jan], [Feb]

If you need specifically to sort the group_code in numerical order, you will need to change the expression in the max function to introduce leading zeros or reformat the data to be lexicographically in sequence. See SqlFiddle for results.

Upvotes: 0

Mike Cole
Mike Cole

Reputation: 14703

You will need to use SQL Server Group By.

Upvotes: 0

Related Questions