Duplicate rows? - 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

General SQL Server Forums

New to SQL Server Programming

Duplicate rows?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-20 : 05:27:13

Hi,

i wanna know, how can i check if i have duplicate rows in my table?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 05:32:25

[code]select col1, col2
from table
group by col1, col2
having count(*) > 1[/code]

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-24 : 08:54:31

try to add Primary key constraint on table. also check on which column u'r addind primary key, it should not be NOT NULL

BMahesh

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-25 : 09:00:15

Hi,

There is a xID column (int, NOT Null, auto incremental) and i also wanna know the duplicate columns xID? How can i do it ? I tried to add xID both select and group by but no result!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-25 : 09:04:48

quote:
Originally posted by raysefo

Hi,

There is a xID column (int, NOT Null, auto incremental) and i also wanna know the duplicate columns xID? How can i do it ? I tried to add xID both select and group by but no result!

What do u mean by duplicate columns? Do you mean duplicate values for column xID?

Harsh Athalye
India.
"Nothing is Impossible"

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-25 : 09:13:52

select fTime, bid
from table
group by fTime, bid
having count(*) > 1

the code above, brings duplicate columns in my table, i also wanna add xID column to this code.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-25 : 11:55:33

rayfeso,

What is duplicate "Column"? U mean duplicate records ?
Give an example of ur duplicate columns (the results u get)

Also give ur table structure and some sample data and what do u expect as the output.

Srinika

Fromper
Starting Member

33 Posts

Posted - 2006-07-25 : 12:45:52

quote:
Originally posted by raysefo

select fTime, bid
from table
group by fTime, bid
having count(*) > 1

the code above, brings duplicate columns in my table, i also wanna add xID column to this code.

Is it just me or is this a very simple question? If I understand the question properly, the answer is to just add a comma and the xID column name after the word "bid" on both lines it appears. ie:

select fTime, bid, xID
from table
group by fTime, bid, xID
having count(*) > 1

Or is there more to this question than that?

--Richard

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-25 : 17:07:08

Since xID is "auto incremental" which I think he mean IDENTITY, xID can't have duplicate values.

Peter Larsson
Helsingborg, Sweden

Fromper
Starting Member

33 Posts

Posted - 2006-07-26 : 14:04:50

Ahh... I get it now. So xID can't be duplicated, but you want to know the xID value of the records where the other fields were duplicated. Got it. That rules out "group by", I think. Use a join instead:

select a.fTime, a.bid, a.xID, b.xID
from table a, table b
where a.fTime = b.fTime
and a.bid = b.bid
and a.xID < b.xID
order by a.fTime, a.bid, a.xID

Is this what you need?

--Richard

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-27 : 03:08:51

i appriciate , thanks lot

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-27 : 04:03:23

Hi,

select a.fTime, a.bid, a.xID
from [temp] a, [temp] b
where a.fTime = b.fTime
and a.bid = b.bid
and a.xID = b.xID
order by a.fTime, a.bid, a.xID

the statement above works fine and gives result as below;
fTime bid xID
2005-01-02 19:53:00.000 1.3543 2303
2005-01-02 19:53:00.000 1.3543 2304
... ... ...
... ... ...
2005-01-02 20:20:00.000 1.3545 3309
2005-01-02 20:20:00.000 1.3545 3310
... ... ...

As you can see from the result set, fTime and bid are duplicate but xID is IDENTITY.What i wanna do is, to write a select statment to get DISTINCT(fTime) but also wanna select lowest or highest (it does not matter) xID like below;

fTime bid xID
2005-01-02 19:53:00.000 1.3543 2303
2005-01-02 20:20:00.000 1.3545 3309
... ... ...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-27 : 08:01:33

>> What i wanna do is, to write a select statment to get DISTINCT(fTime) but also wanna select lowest or highest (it does not matter) xID like below;

Do not Group By xID, Use Max(xID) or Min(xID) in ur select list

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources