Selecting Only the Second Row in a Record Pair 1

[larrydavid]

Hello,

I have output which is as follows:

CompanyID       $_Paid          $_Billed      
22              181.38	        67.88
22              5986.34		   2279.03     
23              181.38	        67.88
23              4936.34	       7279.03   
24              181.38	        67.88
24              9986.34	       1279.03

There will always be two rows per CompanyID. Usually the first value will duplicate throughout all of the rows, but not always so I cannot simply filter in the WHERE clause.

I only want to select the second row for each CompanyID pair of records. for this output:

CompanyID       $_Paid          $_Billed      
22              5986.34	        2279.03     
23              4936.34	        7279.03   
24              9986.34	        1279.03

I don't have an identity column so if the solution requires I add one that's OK.

Any help is greatly appreciated.

Thanks,
Larry

 

[xxing]

Hi Larry

This might help:


if object_id('tempdb..#test') is not null drop table #test
create table #test
(
companyID int
,[$_Paid] decimal(10,2)
,[$_Billed] decimal(10,2)
)

insert into #test values(22,181.38,67.88)
insert into #test values(22,5986.34,2279.03)
insert into #test values(23,181.38,67.88)
insert into #test values(23,4936.34,7279.03)
insert into #test values(24,181.38,67.88)
insert into #test values(24,9986.34,1279.03)

select
x.companyID
,x.[$_Paid]
,x.[$_Billed]
from
(
select
companyID
,[$_Paid]
,[$_Billed]
,row_number() over (partition by companyID order by [$_Paid]) as rownumber
from #test
) x
where rownumber = 2

regards
Mark

 

[larrydavid]

Hi Mark,

Before I read your response I got pretty close to what I needed with this:

SELECT
EXPR2.*
FROM
(
SELECT
Name,
RowNumber,
(EXPR1.RowNumber % 5) AS ROW_MOD
FROM
(
SELECT
Name,
ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber'
FROM
Districts
WHERE
state = 'TX'
) EXPR1
) EXPR2
WHERE
EXPR2.ROW_MOD = 0

But your example uses partition which I like better, and for some reason (which I will research), your example gets me more accurate results.

Thank you for your time, I appreciate the help.

Thanks,
Larry