ELIMINATING DUPLICATE ROWS URGENT

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

General SQL Server Forums

New to SQL Server Programming

ELIMINATING DUPLICATE ROWS URGENT

Author

Topic

sivag
Starting Member

5 Posts

Posted - 2007-07-20 : 01:18:19

Hi i have a table value which contains
value
-----
a
a
a
b
b
b
c
c
c

Now i need to have the results as

a 1

b 1

c 1

I tried using distinct.But OLEDB returns error that invalid syntax.It doesn't support distinct keyword.Actually i read these table from a file thru OLEDB.Not from a database.Any idea ? Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 01:59:51

SELECT DISTINCT Value, 1 as SomeColumnNameHere
FROM Table1

Peter Larsson
Helsingborg, Sweden

sivag
Starting Member

5 Posts

Posted - 2007-07-20 : 02:24:59

Thanks for the reply.Above thing works good but i want a dynamic code.

sivag
Starting Member

5 Posts

Posted - 2007-07-20 : 02:52:05

I have done it with this

select col_name,count(res) from (select col_name,min(col_name) res from table group by col_name) as a group by col_name ;

Anybody having this problem use this.This will be a dynamic one

pootle_flump

1064 Posts

Posted - 2007-07-20 : 03:31:28

That makes little sense and won't return the results you indicated in your first post based on the sample data.

That isn't dynamic code - dynamic code is very different. You have used an aggregate function, in a rather round about way, to work out the number of records per value. You should find the below gives you the same result but more efficiently:

select col_name, count(*) from table group by col_name

This will return


a 3
b 3
c 3

sivag
Starting Member

5 Posts

Posted - 2007-07-20 : 04:46:28

No i want the output as

a 1

b 1

c 1

i.e. i don't want count of each occurrence of the alphabet.Whatever may be the times the alphabet may occur,but i want th result as said above.

pootle_flump

1064 Posts

Posted - 2007-07-20 : 05:00:53

Ah - beg your pardon. Odd requirement though don't you think? Create a query that will always count 1 without hardcoding 1?
Anyway - this would be more efficient:

select col_name, count(DISTINCT col_name) from table group by col_name

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 14:23:43

See answer posted 07/20/2007 : 01:59:51 to you!
I think ODBC needs all columns to be named.

Peter Larsson
Helsingborg, Sweden

sivag
Starting Member

5 Posts

Posted - 2007-07-21 : 06:23:06

Thank You . I resolved it as you people said.Thanks s lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources