query - 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 2000 Forums

SQL Server Development (2000)

query

Author

Topic

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-08 : 12:23:54

I have the following table with the following data


GROUPS (group_name varchar(25))
----------
TEST
TEST-A
DOOR
LIVE-A
HITS
HITS-A

basically im trying to write a query that will return the following results

TEST
DOOR
LIVE-A
HITS

Basically if a group has another record with a '-A' on the end I want to ignore the record with '-A'. I cannot ignore all '-A' records, (notice LIVE-A record has no LIVE record).

Any help is very much appreciated

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-08 : 12:42:47

select group_name
from groups
where group_name not like '%_A'
union
select group_name
from groups
where group_name like '%_A'
and not exists (select *
from groups g2
where replace(g2.group_name,'_A','') = groups.group_name)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/08/2002 12:48:15

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-08 : 12:46:05

select group_name
from groups g1
where len(g1.group_name) =
(select min(len(g2.group_name))
from groups g2
where replace(g1.group_name,'_A','') = replace(g2.group_name,'_A',''))

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-08 : 16:00:43

thanks for the help nr, but the first query doenst work and I cant use len because not all data will be 4 characters without the '-A'. The field is a varchar(25) and could contain that much.

I had


select group_name from groups
where group_name not in
(select group_name + '-A' from groups where group_name not like '%-A')

but with a huge amount of data I think not in is slowing the query down.

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-08 : 16:52:49

Try this. HTH.

-- get all the rows without 'duplicates'
select group_name=max(group_name)
from groups
group by replace(group_name,'-A','')
having count(*) = 1
UNION
-- get all the rows with 'duplicates'
select group_name=replace(group_name,'-A','')
from groups
group by replace(group_name,'-A','')
having count(*) > 1

Edited by - Lou on 02/08/2002 16:54:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources