Query to collect data from previous rows

Question

I have a table with records as, in example data below a CO.Nr are TH-123,Th-456 and so on... I need to collect the data..

Nr.       CO.Nr           Employee       Resp            Description       Date

1         TH-123          ABC            NULL              HELLO           10.05.2010
2         TH-123          NULL           S14               NULL            18.05.2010
3         TH-123          DEF                              NULL            13.05.2010
4         TH-456          XYZ            NULL              NULL             1.07.2010
5         TH-456          NULL           S19               SOME             NULL
6         TH-456                                           TEXT            08.05.2010
7         TH-456                        NULL                               28.05.2010

For TH-123, If Nr. is maximum, that is the record i need to start with group by CO.Nr, so it is the record with Nr as 3, if the value in the other columns is NULL or space, go to a record above that is the record with Nr as 2, even if it has null value go to a record above that record with Nr. as 1 in this case. In the 3 records i need to take the maximum of date. For the above data, i need to have output as,

      CO.Nr           Employee       Resp            Description       Date

      TH-123          DEF            S14               HELLO            18.05.2010
      TH-456          XYZ            S19               TEXT             01.07.2010

Thanks in advance!

Answer 1

You can use a subselect to choose the record you want, then join on that. Something like the following for the employees one (I'll leave the rest of the columns as an exercise):

SELECT MyTable.[CO.Nr], Employees.Employee
FROM MyTable
LEFT OUTER JOIN (SELECT FIRST(Employee) as Employee, [CO.Nr]
                 FROM MyTable
                 WHERE Employee IS NOT NULL AND Employee <> ''
                 GROUP BY [CO.Nr]
                 ORDER BY [Nr.] DESC) Employees 
           ON MyTable.[CO.Nr] = Employees.[CO.Nr]
GROUP BY MyTable.[CO.Nr]

Or, if FIRST() is not a valid aggregate function, as mentioned in your comments, you can try subselects in your SELECT clause, like:

SELECT t.MyTable.[CO.Nr], 
       (SELECT TOP(1) x.Employee
        FROM MyTable x
        WHERE x.[CO.Nr] = t.[CO.Nr]
        AND x.Employee IS NOT NULL AND x.Employee <> ''
        ORDER BY [Nr.] DESC) as Employee
FROM MyTable t
GROUP BY t.[CO.Nr]

Answer 2

You can do it many ways

select [co.nr],
(select top(1) employee from mytable b where b.[co.nr]=a.[co.nr]  and 
                        employee is not null order by nr desc) as employee,
(select top(1) resp from mytable b where b.[co.nr]=a.[co.nr]  and 
                        resp is not null order by nr desc) as resp,
(select top(1) description from mytable b where b.[co.nr]=a.[co.nr]  and 
                        description is not null order by nr desc) as description,
(select max([date]) from mytable b  where b.[co.nr]=a.[co.nr]) as Date
from (
select distinct [co.nr] 
 from mytable ) as a