Select statement to return row comparison results

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)

Select statement to return row comparison results

Author

Topic

skinch
Starting Member

4 Posts

Posted - 2009-06-01 : 08:49:23

I need a select statement that checks to see if the are noteid of equal value for an inquiry number. If there is a matching noteid then choose the row with note_type like 'PP%'

Here is a sample dataset

Inq # Description Line NOTEID Note_TYPE Note_Text
15288 Connector 1 531078 Purchasing Connector
15288 Connector 1 531078 PP003 Ship by the
15288 Adapter 2 531098 Purchasing Adapter
15288 Adapter 3 531100 Purchasing Adapter

Sample of desired output:

15288 Connector 1 531078 PP003 Ship by the
15288 Adapter 2 531098 Purchasing Adapter
15288 Adapter 3 531100 Purchasing Adapter

Basically, if there are two NOTEID of equal value then select the row with Note_type like 'PP%'
Any suggestions will be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-01 : 09:23:02

[code]
select *
from
(
select *, row_no = row_number() over (partition by [Inq #], [Description], [Line], [NOTEID]
order by case when Note_TYPE like 'PP%' then 1 else 2 end, Note_TYPE)
from data
) t
where t.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-01 : 09:28:07

try this one
select * from (
select row_number()over(partition by noteid order by inq#)as rid, * from @t)s
where note_type like case when rid>1 then '%pp%' end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:46:31

quote:
Originally posted by bklr

try this one
select * from (
select row_number()over(partition by noteid order by inq#)as rid, * from @t)s
where note_type like case when rid>1 then '%pp%' end

will this work if it has a single record with note_type like pp?

skinch
Starting Member

4 Posts

Posted - 2009-06-02 : 12:38:02

I am afraid this did not give me the desired output. Basically, What I get is the same four rows I used in the example with a row_id. What I need to do is select the rows that do not have duplicate note_id and if there are duplicate note_ids then select the row with output type %PP%

quote:

Originally posted by khtan


select *
from
(
    select *, row_no = row_number() over (partition by [Inq #], [Description], [Line], [NOTEID]
                                          order by case when Note_TYPE like 'PP%' then 1 else 2 end, Note_TYPE)
    from   data
) t
where t.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources