group by problem

[Finn J Johnsen]

Hi

I have a problem with the following case.

create table stats(
compId integer not null,
compValue integer,
statTime date not null
);
ALTER TABLE stats ADD PRIMARY KEY (compID, statTime)

(example data)
insert into stats value(1,10,'01.01.2002');
insert into stats value(1,11,'02.01.2002');
insert into stats value(1,10,'03.01.2002');
insert into stats value(1,11,'04.01.2002');
insert into stats value(1,10,'05.01.2002');
insert into stats value(2,11,'04.01.2002');
insert into stats value(2,10,'05.01.2002');

The problem is to extract the (entire) "newest" records per compid.

If I do this:

select compID, max(statTime)
from stats
group by compID

I get the newest record, but I'm missing compValue:

select compID, compValue, max(statTime)
from stats
group by compID, compValue

Now I'm in trouble ! I've got an compID per compValue. This is my
problem. How can I "hook" the compValue on here....

This could be solved in MySQL, by "brutally" cutting the recordset. But
interbase 6.0/Firebird 1.0 hasn't implemented the "limit" statement.
Which isn't very standard by the way.

select * from stats
order by statTime
limit (select count(compID) from stats)

Regars,
Finn J Johnsen

[Andy Hassall]

On Sat, 24 Aug 2002 14:25:51 +0200, Finn J Johnsen <finn@_mobitech.no> wrote:

>create table stats(
>compId integer not null,
>compValue integer,
>statTime date not null
>);
>ALTER TABLE stats ADD PRIMARY KEY (compID, statTime)
>
>(example data)
>insert into stats value(1,10,'01.01.2002');
>insert into stats value(1,11,'02.01.2002');
>insert into stats value(1,10,'03.01.2002');
>insert into stats value(1,11,'04.01.2002');
>insert into stats value(1,10,'05.01.2002');
>insert into stats value(2,11,'04.01.2002');
>insert into stats value(2,10,'05.01.2002');
>
>The problem is to extract the (entire) "newest" records per compid.
>

>This could be solved in MySQL, by "brutally" cutting the recordset. But
>interbase 6.0/Firebird 1.0 hasn't implemented the "limit" statement.
>Which isn't very standard by the way.

SELECT s1.compId, s1.compValue
FROM stats s1
WHERE s1.statTime = (SELECT MAX(statTime)
FROM stats s2
WHERE s1.compId = s2.compId)

--
Andy Hassall (an...@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyhsoftware.co.uk/space | disk usage analysis tool

[Finn J Johnsen]

Yepp, thats it. Thanks

[Greg Scott]

Usually, when you write a subquery, there is an alternate way to achieve the
same thing with a join, instead. While these may be equivilent in terms of
the result set, you may see a difference in efficiency, where the join
version may be quicker.
"Finn J Johnsen" <finn@_mobitech.no> wrote in message
news:3D69FD21.4040909@_mobitech.no...