Help with SQL query

[SBpsc]

Using MySQL 4.1

I have the following table:

Stat | StatDt | ChkDt
=========================
1 | 8/3 | 8/3
NULL | NULL | 8/7
23 | 9/12 | 9/12
5 | 9/17 | 9/17
NULL | NULL | 9/25
NULL | NULL | 10/1

If a stat is available (NOT NULL value), the StatDt will be equal to the ChkDt. Anytime I have a NULL Stat, I need to display the previous non NULL stat value for it. In other words, I need my display to look like the following:

Stat | StatDt | ChkDt
=========================
1 | 8/3 | 8/3
1 [/color red] | 8/3 [/color red] | 8/7
23 | 9/12 | 9/12
5 | 9/17 | 9/17
5 [/color red] | 9/17 [/color red] | 9/25
5 [/color red] | 9/17 [/color red] | 10/1

Can anyone suggest any ideas on how I can go about solving this issue?

Thanks.

 

[feherke]

Hi

Quite ugly, with some assuptions : chkdt is not null, unique and ascending. Also assuming that chkdt is of type [tt]date[/tt]. Of course, if you have a primary key in that table, use it instead.

[b]select[/b]
coalesce(stat,([b]select[/b] stat [b]from[/b] sbpsc [b]where[/b] chkdt<s.chkdt [b]and[/b] stat [b]is not null order by[/b] chkdt [b]desc limit[/b] 1)) stat,
coalesce(statdt,([b]select[/b] statdt [b]from[/b] sbpsc [b]where[/b] chkdt<s.chkdt [b]and[/b] statdt [b]is not null order by[/b] chkdt [b]desc limit[/b] 1)) statdt,
chkdt

[b]from[/b] sbpsc s;

Feherke.

http://rootshell.be/~feherke/

 

[SBpsc]

Thanks Feherke.

It worked great. I got the results I wanted.