Roll up results

Hi everyone,

5 samples are analyzed for lead in three different tests. Not every
sample is necessarily analyzed in each test, resulting in gaps in the
table.

create table results
(
testnbr int not null,
sampleid char( not null,
result decimal(5,1),
primary key (testnbr, sampleid)
)

insert into results(testnbr, sampleid, result) values (1, 'sample 1',
1.1)
insert into results(testnbr, sampleid, result) values (1, 'sample 2',
null)
insert into results(testnbr, sampleid, result) values (1, 'sample 3',
null)
insert into results(testnbr, sampleid, result) values (1, 'sample 4',
4.
insert into results(testnbr, sampleid, result) values (1, 'sample 5',
null)

insert into results(testnbr, sampleid, result) values (2, 'sample 1',
1.7)
insert into results(testnbr, sampleid, result) values (2, 'sample 2',
2.4)
insert into results(testnbr, sampleid, result) values (2, 'sample 3',
null)
insert into results(testnbr, sampleid, result) values (2, 'sample 4',
null)
insert into results(testnbr, sampleid, result) values (2, 'sample 5',
5.5)

insert into results(testnbr, sampleid, result) values (3, 'sample 1',
null)
insert into results(testnbr, sampleid, result) values (3, 'sample 2',
2.6)
insert into results(testnbr, sampleid, result) values (3, 'sample 3',
1.9)
insert into results(testnbr, sampleid, result) values (3, 'sample 4',
null)
insert into results(testnbr, sampleid, result) values (3, 'sample 5',
5.3)


Given the above I'm trying to return a table where the individual
results are 'rolled-up' towards the top of the list to fill in the
blanks. The output should look like this:

1 sample 1 1.1
1 sample 2 2.4
1 sample 3 1.9
1 sample 4 4.8
1 sample 5 5.5
2 sample 1 1.7
2 sample 2 2.6
2 sample 3 null
2 sample 4 null
2 sample 5 5.3
3 sample 1 null
3 sample 2 null
3 sample 3 null
3 sample 4 null
3 sample 5 null

so that nulls are pushed down to the bottom of the list while numeric
results are pushed to the top. I can't find a pure SQL solution for
this? Does one even exit? Any help would be appreciated.

On Nov 28, 12:00 am, Plamen Ratchev <Pla... DeleteThis @SQLStudio.com> wrote:
> You can adjust the test number sequence based on the result values. Here
> is one method to accomplish this (requires SQL Server 2005/2008):
>
> SELECT ROW_NUMBER() OVER(PARTITION BY sampleid
>                           ORDER BY CASE
>                                      WHEN result IS NULL
>                                      THEN 2147483647
>                                      ELSE testnbr
>                                    END) AS testnbr,
>         sampleid, result
> FROM results;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Hi Plamen, many thanks for your help. Yes, that looks like it will
work. Since I posted I think I've come up with a solution that will
also work in SQL 2000:

select B.testnbr, B.sampleid, A.result
from
(
select r.sampleid, r.result,
(
select count(*) from results r2
where r2.sampleid = r.sampleid
and r2.result is not null
and r2.testnbr <= r.testnbr
) as testnbr
from results r
where r.result is not null
) as A right join
(
select r.sampleid, r.testnbr
from results r
) as B on A.sampleid = B.sampleid
and A.testnbr = B.testNbr