asked on

T-SQL Duplicate Names + Age data

Hi,

Suppose I have the following database:

CREATE TABLE v_GS_SYSTEM
(
Name0 NVARCHAR(250),
Age int
)

INSERT INTO v_GS_SYSTEM (Name0,Age) VALUES ('Jamie',10)
INSERT INTO v_GS_SYSTEM (Name0,Age) VALUES ('Paul',12)
INSERT INTO v_GS_SYSTEM (Name0,Age) VALUES ('Jamie',8)
INSERT INTO v_GS_SYSTEM (Name0,Age) VALUES ('Steve',4)
INSERT INTO v_GS_SYSTEM (Name0,Age) VALUES ('David',5)
INSERT INTO v_GS_SYSTEM (Name0,Age) VALUES ('Steve',22)

How can I list only the names + ages where the name is repeated more then once + a counter.

So a result set something like:

Name0 Age Count
---------- ---- --------
Jamie 8 1
Jamie 10 2
Steve 4 1
Steve 22 2

Even this would be ok:
Name0 Age Count
---------- ---- --------
Jamie 8 2
Jamie 10 2
Steve 4 2
Steve 22 2

As long as I can get the total for each Name

Thanks,

Ward

Guy Hengel [angelIII / a3]

this would do:

select Name0      , age, row_number() over (partition by Name0      order by age) [count]
  from v_GS_SYSTEM
  order by name0, age

Open in new window

Ioannis Paraskevopoulos

Hi,

1st option

SELECT	T.Name0,Age,ROW_COUNT
FROM
	(
		SELECT	Name0,
				COUNT(*) AS ROW_COUNT
		FROM	v_GS_SYSTEM
		GROUP	BY Name0
	) T
	JOIN v_GS_SYSTEM V
	ON V.Name0 = T.Name0

Open in new window

2nd option

SELECT	Name0,
		Age,
		RANK() OVER (PARTITION BY Name0 ORDER BY Name0,Age) 
FROM	v_GS_SYSTEM

Open in new window

Giannis

whorsfall

ASKER

Hi,

Thanks for your answers but I wanted to make sure it will only display records where the name is repeated more than once.

So Paul and David would not get selected.

Thanks,

Ward

Guy Hengel [angelIII / a3]

sorry ...

select Name0      , age, row_number() over (partition by Name0      order by age) [count]
  from v_GS_SYSTEM
 where Name0 IN ( SELECT Name0 FROM v_GS_SYSTEM Where Name0 is not null GROUP BY name0  having count(*) > 1 )
  order by name0, age

Open in new window

Ioannis Paraskevopoulos

SELECT	T.Name0,Age,ROW_COUNT
FROM
	(
		SELECT	Name0,
				COUNT(*) AS ROW_COUNT
		FROM	v_GS_SYSTEM
		GROUP	BY Name0
		HAVING   COUNT(*)>1
	) T
	JOIN v_GS_SYSTEM V
	ON V.Name0 = T.Name0

Open in new window

ASKER CERTIFIED SOLUTION

appari

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer