Question: same code, diferent name

Question

Question: same code, diferent name

carlosalvarado345

Grasshopper

Points: 14
More actions
February 17, 2009 at 8:42 am

#155284

Hi,
I'm newbie in SQL and I have a question for you:
I have a tabla in this way:
code name
1 a
1 a
1 a.
1 a..
2 b
2 b.
2 bd
2 b
3 c
3 cc
3 c.
3 c
As you see, I have some code (for example 1) but the name that corresponds to the code sometimes change (a or a. or a.. or something else) I need to group the code and the most repeated name for each code, so the output of my query must be:
Code Name
1 a
2 b
3 c
How can I do that?
Thanks for your answers.
Carlos.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

psuedo code sucks big time.

what does the REAL data look like? if you just want the first character, or do you want to strip out non- letter stuff fromt he data?

typically you would be

select code,name, group by code,name

if it was just the first character you want, it's

select code,left(name,1), group by code,left(name,1)

give us real world example data of what you want to get a better answer.

carlosalvarado345 (2/17/2009)
Hi,
I'm newbie in SQL and I have a question for you:
I have a tabla in this way:
code name
1 a
1 a
1 a.
1 a..
2 b
2 b.
2 bd
2 b
3 c
3 cc
3 c.
3 c
As you see, I have some code (for example 1) but the name that corresponds to the code sometimes change (a or a. or a.. or something else) I need to group the code and the most repeated name for each code, so the output of my query must be:
Code Name
1 a
2 b
3 c
How can I do that?
Thanks for your answers.
Carlos.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

carlosalvarado345 Grasshopper Points: 14 More actions · Answer 2

Ok, sorry,

the real data looks like:

Code Name

75360412 Juan Arias Mejia.

75360412 Juan Arias Mejia S.A

75360412 Juan Arias Mejia

75360412 Juan Arias Mejia SA.

87245354 Luis Prada Guerra

87245354 Luis Prada Guerra Peña

87245354 Luis Prada Guerra S.A.

24587638 Metalurgica Alvares Lt

24587638 Metalurgica Alvares Ltda.

24587638 Metalurgica Alvares Ltda

24587638 Metalurgica Alvares

As you can see the names are diferents. I need to select most repeated name for each code, so for the table below the output must be:

Code Name

75360412 Juan Arias Mejia S.A

87245354 Luis Prada Guerra Peña

24587638 Metalurgica Alvares

Regards.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok, what happens int eh case of a tie?...for code 75360412, two names have been used twice in your example

results:

code name cnt

75360412 Juan Arias Mejia 2

75360412 Juan Arias Mejia S.A 2

75360412 Juan Arias Mejia SA. 1

WITH MYCTE AS (

SELECT '75360412' AS [Code], 'Juan Arias Mejia' AS [Name] Union All

SELECT '75360412' AS [Code], 'Juan Arias Mejia S.A' AS [Name] Union All

SELECT '75360412' AS [Code], 'Juan Arias Mejia' AS [Name] Union All

SELECT '75360412' AS [Code], 'Juan Arias Mejia SA.' AS [Name] Union All

SELECT '87245354' AS [Code], 'Luis Prada Guerra' AS [Name] Union All

SELECT '87245354' AS [Code], 'Luis Prada Guerra Peña' AS [Name] Union All

SELECT '87245354' AS [Code], 'Luis Prada Guerra S.A.' AS [Name] Union All

SELECT '24587638' AS [Code], 'Metalurgica Alvares Lt' AS [Name] Union All

SELECT '24587638' AS [Code], 'Metalurgica Alvares Ltda.' AS [Name] Union All

SELECT '24587638' AS [Code], 'Metalurgica Alvares Ltda' AS [Name] Union All

SELECT '24587638' AS [Code], 'Metalurgica Alvares' AS [Name] Union All

SELECT '24587638' AS [Code], 'Metalurgica Alvares' AS [Name] )

select ,[name], count(name) as cnt

from mycte

group by code, [name]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

carlosalvarado345 Grasshopper Points: 14 More actions · Answer 4

ok, what happens int eh case of a tie?

Good question, I supose I can select any of the options.

This code:

select ,[name], count(name) as cnt
from mycte
group by code, [name]

Returns:

Code Name cnt
75360412 Juan Arias Mejia.1
75360412 Juan Arias Mejia S.A 2
75360412 Juan Arias Mejia 1
75360412 Juan Arias Mejia SA. 1
87245354 Luis Prada Guerra 1
87245354 Luis Prada Guerra Peña 2
87245354 Luis Prada Guerra S.A. 1
24587638 Metalurgica Alvares Lt 1
24587638 Metalurgica Alvares Ltda. 1
24587638 Metalurgica Alvares Ltda 1
24587638 Metalurgica Alvares 2

But now, what can I do to select only the rows with the most repeated name for each code?

to obtain the next result:

Code Name cnt
75360412 Juan Arias Mejia S.A 2
87245354 Luis Prada Guerra Peña 2
24587638 Metalurgica Alvares 2

Regards.

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 5

hi,

How about this for a solution:

-- my table definition

create table #Names (code int, [name] varchar(256))

-- do inserts...

-- use row number and sort to select top 1 in each code.

-- Note, you can change the order by clause to meet your needs

select , [name]

from

(select ,[name], count(name) as cnt ,

row_number () over (PARTITION BY code ORDER BY count(name) desc, [name]) as rownum

from #names

group by code, [name]) a

where rownum=1

B

carlosalvarado345 Grasshopper Points: 14 More actions · Answer 6

hi,
How about this for a solution:
-- my table definition
create table #Names (code int, [name] varchar(256))
-- do inserts...
-- use row number and sort to select top 1 in each code.
-- Note, you can change the order by clause to meet your needs
select , [name]
from
(select ,[name], count(name) as cnt ,
row_number () over (PARTITION BY code ORDER BY count(name) desc, [name]) as rownum
from #names
group by code, [name]) a
where rownum=1
B

Thanks Bevan!!!!! this solution worked very well. 😀

Regards.