Query Help. - 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)

Query Help.

Author

Topic

mummy
Starting Member

9 Posts

Posted - 2009-02-27 : 03:57:24

Hi All,

I have a table like below:

Status TicketNo
----------------
S XYZ444
S DSL314
N ABC400
S ABC400
N JXM542
S JXM542
N XMN005
S SDD002
S XKK003

i would like to remove records(bold ones above) where the Status is "S" for same "TicketNo".

Expected Results:

Status TicketNo
----------------
S XYZ444
S DSL314
N ABC400
N JXM542
N XMN005
S SDD002
S XKK003

Can anyone please help me to solve this problem out.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-27 : 03:59:03

try this
select min(status),ticketno from tablename group by ticketno

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-27 : 04:23:20

Check this once,

declare @temp table ( Status varchar(32),TicketNo varchar(32))
insert into @temp
select 'S','XYZ444' union all
select 'S','DSL314' union all
select 'N','ABC400' union all
select 'S','ABC400' union all
select 'N','JXM542' union all
select 'S','JXM542' union all
select 'N','XMN005' union all
select 'S','SDD002' union all
select 'S' ,'XKK003'

delete t from
( select status,ticketno,row_number() over ( partition by ticketno order by ticketno) as rn from @temp) t
where t.rn > 1

select * from @temp

mummy
Starting Member

9 Posts

Posted - 2009-02-27 : 06:08:40

quote:
Originally posted by bklr

try this
select min(status),ticketno from tablename group by ticketno

Hello sir, I have 1 more column in the table with 'ticketcount' and need to remove duplicate values based on
1)'status':- what ever counter, if 'N' & 'S' for same ticket then 'S' should be removed
2)'counter':- if status and ticketno are same with status 'S' then should check the 'counter' and remove the smaller counter.

bold ones should be removed.

Counter Status TicketNo
-----------------------
10 S XYZ444
11 S DSL314
12 N ABC400
13 S ABC400
14 N JXM542
15 S JXM542
10 N XMN005
11 S SDD002
14 S XKK003
15 S XKK003
18 S UVW333
17 S UVW333

expected results like this:

Counter Status TicketNo
-----------------------
10 S XYZ444
11 S DSL314
12 N ABC400
14 N JXM542
10 N XMN005
11 S SDD002
15 S XKK003
18 S UVW333

Please help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources