Have Column with IF statement in a subquery - how to select?

[smacream]

Hi,

I'm trying to construct a query that checks if two (or more) consecutive values for a field are the same, then I only select the first one.

I found this suggestion:

http://www.artfulsoftware.com/infotree/queries.php?&bw=1259#798

This constructs a table using:
create table t (
p char(3),
d date
);
insert into t values
('50%','2008-05-01'),
('30%','2008-05-02'),
('30%','2008-05-03'),
('50%','2008-05-04'),
('50%','2008-05-05'),
('20%','2008-05-06'),
('20%','2008-05-07'),
('50%','2008-05-08'),
('70%','2008-05-09'),
('70%','2008-05-10');

Then it performs the query:
set @p='';
set @d='';
select p 'Pct Changed',d Date from (
select
p,
if( p<>@p, d, @d ) as d,
@p:=p,
@d:=d
from t
order by d
) as t
group by d;
which should produce the desired result.

When I attempted to replicate this example, the second column 'Date' just gave [BLOB - 10B] although the first column seemed to order ok.

If I just look at the subquery, I still got [BLOB - 10B] for that column.

I modified the subquery to 1) try and resolve the 'BLOB' issue, and 2) to help cater for situations where more than two consecutive rows have same value (the above only worked for 2 consecutive rows) by adding an additional IF statement. The query became:
set @p='';
set @d='';
select
p,
if ( p<>@p, d, @d ) AS 'd1',
If (p<>@p, @d:=d, @d),
@p:=p
from t
order by d
The results seemed exactly as I would want. I then tried to select the column in an outer query as follows:
set @p='';
set @d='';
select `p` AS 'Pct Changed', `d1` AS 'Date' from (
select
p,
if ( p<>@p, d, @d ) AS 'd1',
If (p<>@p, @d:=d, @d),
@p:=p
from t
order by `d`
) as tmp
group by `d1`;
But get [BLOB - 10B] values for d1 column even if I remove group by clause.

Does anyone know how I can resolve this. Using 5.0.67 version of MySQL. Would be grateful for any helpful suggestions.

Seosamh

 

[smacream]

P.S. I forgot to say that I was using the phpmyadmin interface. I just used the command line and my squery seems to work fine. So it must be related to phpmyadmin.