Eliminate repeating ID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Eliminate repeating ID

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 04:14:11

Hi.,
I dont know to eliminate the repeting record in the ID column how to do that.,

for ex., i have given an example.,

create table Example (ID int, Name varchar(30))

INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag')
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')

select * from Example.,

i will get.,

ID Name
----------- ------------------------------
1 Chirag
1 Shailesh
2 Dipak
4 Mihir
4 Piyush

.....
but i need like

ID Name
----------- ------------------------------
1 Chirag
Shailesh
2 Dipak
4 Mihir
Piyush

I dont want repeated ID., How can i do that.,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-17 : 04:52:05

quote:
Originally posted by karthik_padbanaban

Hi.,
I dont know to eliminate the repeting record in the ID column how to do that.,

for ex., i have given an example.,

create table Example (ID int, Name varchar(30))

INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag')
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')

select * from Example.,

i will get.,

ID Name
----------- ------------------------------
1 Chirag
1 Shailesh
2 Dipak
4 Mihir
4 Piyush

.....
but i need like

ID Name
----------- ------------------------------
1 Chirag
Shailesh
2 Dipak
4 Mihir
Piyush

I dont want repeated ID., How can i do that.,

Where do you want to show data?
The feature is called Suppress if duplicated which you should do in your front end application

Madhivanan

Failing to plan is Planning to fail

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 04:59:20

Thank u for ur reply but., In my appication i have to do from sql server how can i do
.,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-17 : 05:36:36

quote:
Originally posted by karthik_padbanaban

Thank u for ur reply but., In my appication i have to do from sql server how can i do
.,

What is the application you are using?

Madhivanan

Failing to plan is Planning to fail

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 05:36:54

No i am not using any front
I am using only - sql server 2005

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-17 : 06:41:22

try this,

DECLARE @Example TABLE (ID INT, Name VARCHAR(30))

INSERT INTO @Example (ID, Name) VALUES (1,'Chirag')
INSERT INTO @Example (ID, Name) VALUES (1,'Shailesh')
INSERT INTO @Example (ID, Name) VALUES (2,'Dipak')
INSERT INTO @Example (ID, Name) VALUES (4,'Mihir')
INSERT INTO @Example (ID, Name) VALUES (4,'Piyush')

SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) = 1 THEN ID ELSE NULL END AS 'ID', Name
FROM @Example

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 06:45:42

I am getting error like this.

'ROW_NUMBER' is not a recognized function name.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-17 : 06:53:12

what version of SQL you are using?
2000 or 2005-- the above works in 2005

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 07:02:22

I am using SQL SERVER 2005

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 07:09:54

I think the compatibility level is set to 80.
If you want to set it to 90 :-
Right click database > goto properties > select options.
You will find compatibility level drop down.Change it to 90.
The row_number()function will work.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 07:16:42

Its there up to 80 only.,

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 07:28:56

Then I guess row_number wont work.You will I have to ask your DBA about the compatibilty level.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-17 : 07:30:47

Hi,

Try with This
Declare @Temp Table (Col1 Int, Col2 Int, Names Varchar(100))
Insert Into @Temp
Select 1,2,'A' union all
Select 2,2,'B' union all
Select 3,2,'C' union all
Select 4,3,'D' union all
Select 5,3,'E' union all
Select 6,3,'F' union all
Select 7,4,'G' union all
Select 8,4,'H'

Select distinct Col2, (Select Top 1 Col1 From @Temp D where D.Col2 = T.Col2 )AS 'Col1' into #Temp
From @Temp T

Select Isnull(cast(B.col2 as varchar(10)),' ') Col2, Names
From @Temp A
Left join #Temp B ON (B.col1 = A.col1 and B.col2 = A.col2)

Drop table #temp

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-17 : 07:52:17

Thanks a lot Ranganath it worked.,.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-17 : 08:58:27

quote:
Originally posted by karthik_padbanaban

No i am not using any front
I am using only - sql server 2005

So this is only for practice?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources