Removing a server: distinct

Showing posts with label distinct. Show all posts

Wednesday, March 7, 2012

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Removing Duplicate Rows Issue

I'm trying to remove duplicate rows from a table. tableA has 2 cols. acctid
and col1 When I run:
select count(distinct accid) from tableA
I get 10 as a result(I guess there are 10 distinct rows in the table,
correct?), but when I run:
select distinct acctid, col2 into #temp from tableA
(those are the only 2 columns in the table) I get 11 as the result, How
come? Shouldn't it say 10 rows affexted and not 11? any ideas?
thanksCan you show us the structure of tableA, and the INSERT statements required
to populate it with these 10 or 11 rows?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> I'm trying to remove duplicate rows from a table. tableA has 2 cols.
acctid
> and col1 When I run:
> select count(distinct accid) from tableA
> I get 10 as a result(I guess there are 10 distinct rows in the table,
> correct?), but when I run:
> select distinct acctid, col2 into #temp from tableA
> (those are the only 2 columns in the table) I get 11 as the result, How
> come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> thanks|||tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
when I do select count(distinct acctid) from tableA I get 10 rows as the
result, then when I select distinct aactid, col1 into #temp from tablea I get
11 rows, Why is this the case? Thank you
"Aaron [SQL Server MVP]" wrote:
> Can you show us the structure of tableA, and the INSERT statements required
> to populate it with these 10 or 11 rows?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> acctid
> > and col1 When I run:
> > select count(distinct accid) from tableA
> > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > correct?), but when I run:
> > select distinct acctid, col2 into #temp from tableA
> > (those are the only 2 columns in the table) I get 11 as the result, How
> > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > thanks
>
>|||Because you added a column to your distinct clause in the second query.
select distinct aactid, col1 from tablea
vs
select distinct aactid from tablea
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> when I do select count(distinct acctid) from tableA I get 10 rows as the
> result, then when I select distinct aactid, col1 into #temp from tablea I
get
> 11 rows, Why is this the case? Thank you
> "Aaron [SQL Server MVP]" wrote:
> > Can you show us the structure of tableA, and the INSERT statements
required
> > to populate it with these 10 or 11 rows?
> > http://www.aspfaq.com/5006
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > acctid
> > > and col1 When I run:
> > > select count(distinct accid) from tableA
> > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > correct?), but when I run:
> > > select distinct acctid, col2 into #temp from tableA
> > > (those are the only 2 columns in the table) I get 11 as the result,
How
> > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > thanks
> >
> >
> >|||that was an example, what I'm really trying to do is remove duplicate by
these statements
select distinct acctid, col1 into #temp from tableA
truncate table tableA
insert into tableA select * from #temp
the logic is in the previous posts!
"Jeff Dillon" wrote:
> Because you added a column to your distinct clause in the second query.
> select distinct aactid, col1 from tablea
> vs
> select distinct aactid from tablea
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> > when I do select count(distinct acctid) from tableA I get 10 rows as the
> > result, then when I select distinct aactid, col1 into #temp from tablea I
> get
> > 11 rows, Why is this the case? Thank you
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > Can you show us the structure of tableA, and the INSERT statements
> required
> > > to populate it with these 10 or 11 rows?
> > > http://www.aspfaq.com/5006
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > > acctid
> > > > and col1 When I run:
> > > > select count(distinct accid) from tableA
> > > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > > correct?), but when I run:
> > > > select distinct acctid, col2 into #temp from tableA
> > > > (those are the only 2 columns in the table) I get 11 as the result,
> How
> > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > thanks
> > >
> > >
> > >
>
>|||Yes, your logic is wrong, per my response.
You are aware that distinct acctid, col1 means take (acctid, col1) together,
and find all distinct rows. Distinct doesn't only apply to the first column
before your comma
To find duplicates:
select count(id), id from a
group by id
having count(id) > 1
Jeff
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:2B455174-0A8A-47E3-BB71-AA26CBE37D0E@.microsoft.com...
> that was an example, what I'm really trying to do is remove duplicate by
> these statements
> select distinct acctid, col1 into #temp from tableA
> truncate table tableA
> insert into tableA select * from #temp
> the logic is in the previous posts!
> "Jeff Dillon" wrote:
> > Because you added a column to your distinct clause in the second query.
> >
> > select distinct aactid, col1 from tablea
> >
> > vs
> >
> > select distinct aactid from tablea
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > > tableA has 2 columns acctid and col1. lets say there are 20 rows of
data,
> > > when I do select count(distinct acctid) from tableA I get 10 rows as
the
> > > result, then when I select distinct aactid, col1 into #temp from
tablea I
> > get
> > > 11 rows, Why is this the case? Thank you
> > >
> > > "Aaron [SQL Server MVP]" wrote:
> > >
> > > > Can you show us the structure of tableA, and the INSERT statements
> > required
> > > > to populate it with these 10 or 11 rows?
> > > > http://www.aspfaq.com/5006
> > > >
> > > > --
> > > > http://www.aspfaq.com/
> > > > (Reverse address to reply.)
> > > >
> > > >
> > > >
> > > >
> > > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > > I'm trying to remove duplicate rows from a table. tableA has 2
cols.
> > > > acctid
> > > > > and col1 When I run:
> > > > > select count(distinct accid) from tableA
> > > > > I get 10 as a result(I guess there are 10 distinct rows in the
table,
> > > > > correct?), but when I run:
> > > > > select distinct acctid, col2 into #temp from tableA
> > > > > (those are the only 2 columns in the table) I get 11 as the
result,
> > How
> > > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
> >

Saturday, February 25, 2012

Removing dupes (distinct like)

Greets, prob. simple problem for experts but my sql is at it's infancy :)

select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID

Gives me:
5266
5266
5266
5088
5088

etc, but I want only unique entity oids returned:
5266
5088

How I can achieve this w/ SQL as distinct no workie bcause of order by?

Thanks
--POTry a GROUP BY instead of ORDER BY. It should still force the sort.|||I'd use both GROUP BY and ORDER BY to get what you've described.

-PatP|||select Entity.OID from Entity
,EN,ENXP as ENXP0

where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
group by ENXP0.ST_NAMEROW,Entity.OID
order by ENXP0.ST_NAMEROW,Entity.OID

(and other combinations) seems still produce exact same dupe OID's.

group by Entity.OID
order by ENXP0.ST_NAMEROW,Entity.OID

won't work (on SQL Server 2000 atleast as ENXP0.ST_NAMEROW must be included also on group by)

Any other suggestions?

--PO|||Did you try a subselect?

select distinct Entity.OID from
(select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID)|||Yepp, unfortunately SQL server does not allow order by's at subselects (otherwise prob could be solved easily ;)

Server: Msg 1033, Level 15, State 1, Line 10
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

:(

TOP stuff is not really applicable cause I must fetch all rows possible and seems that SQL server does not understand it inside inline select.

Damn, this *looks* like piece of cake to solve...|||what does "distinct no workie bcause of order by" mean?

of course you can have DISTINCT and ORDER BY

however, since you are returning only one column, it seems rather unnecessary

rule of thumb: select what you want to order

if you want to order your results by order by ENXP0.ST_NAMEROW and Entity.OID, then you should select both of these columns

so, what was your question again?|||what does "distinct no workie bcause of order by" mean?

of course you can have DISTINCT and ORDER BY

however, since you are returning only one column, it seems rather unnecessary

rule of thumb: select what you want to order

if you want to order your results by order by ENXP0.ST_NAMEROW and Entity.OID, then you should select both of these columns

so, what was your question again?

"Distinct no workie" means that for example this won't ofcourse work:

select distinct Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Anyway we need those OID's and *only unique* OID's ordered by ENXP0.ST_NAMEROW. OID's are later used on another Query to fetch other rows from db (on that particular order made by ENXP0.ST_NAMEROW). Reason is that our system is using SQL generator to generate SQL clauses and reason for that is that writing SQL by hand is far too slow and difficult todo against schema we are using.

Thanks,|||i think you're missing the point

here are a bunch of OIDs --

5266
5088
5937
5013

how can you tell what their ENXP0.ST_NAMEROW values are? you can't

therefore how would you know if they were, or were not, in ENXP0.ST_NAMEROW order? you can't

you simply cannot order OID values by ENXP0.ST_NAMEROW|||For example:

select ENXP.ST_NAMEROW from ENXP
where ENXP.TOID in (5266)

(as we have duplicate main oid on ENXP table)

and if we can get actual OID's ordered by ENXP.ST_NAMEROW using query like:

select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID

which orders them by ENXP0.ST_NAMEROW so do we have OID's ordered by NAMEROW? I think so, but with alot of duplicate values because name is formed using multiple ENXP0.ST_NAMEROW's.|||well, good luck to you sir, i do not understand what you're doing and i cannot help you further|||well, good luck to you sir, i do not understand what you're doing and i cannot help you further

Yep, thanks.
Well basic question was that is there way using SQL to remove duplicate OID's from resultset what this query return (and it must return only OID's ordered by ENXP0.ST_NAMEROW) :

select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW

Result:
5266
5266
5266

Can be done ofcourse on server side as last resort if cannot be done using SQL.

Query like:
select distinct Entity.OID,ENXP0.ST_NAMEROW from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID

Will return for example:
5266 P
5266 Pamela
5266 Prognost

But again we need only those unique OID's returned, no other data at this point.

Wednesday, March 7, 2012

removing embedded duplicate rows

Removing Duplicate Rows Issue

Removing Duplicate Rows Issue

Saturday, February 25, 2012

Removing dupes (distinct like)

Removing a server

Blog Archive

About Me