find rows with latest date associated with a given field

[Walter Moore]

Well, I've found 2 methods of doing this in isql:
select * from mytable group by id having move_by = max(move_by)
and

select * from mytable t1 where move_by in (select max(move_by) from
mytable t2 where t1.id = t2.id group by id)

The problem is, when I try to use Sybase's Java JDBC JConnect 3.1, the first
method only returns the 1st row, while the second works correctly.

Why is this?

Walter

Walter Moore wrote:

> Hi all!
>
> My date looks like:
> id time_moved moved_to blah blah ...
> abc123 12/14/1999 00:00:01 MEM asdas dasdasdasdasd
> abc123 01/05/2000 00:10:00 MEM asdas adasdasd
> abc123 01/17/2000 00:14:00 LAX ddedd dsdasda
> zzz123 12/14/1999 00:00:01 MEM asdas dasdasdasdasd
> zzz123 01/05/2000 00:10:00 MEM asdas adasdasd
> zzz123 01/17/2000 00:14:00 LAX ddedd dsdasda
> abc234 12/20/1999 10:00:00 MEM asdas dasdasdasdasd
> abc234 01/04/2000 02:10:00 EWR asdas adasdasd
> abc234 01/07/2000 00:14:00 LAX ddedd dsdasda
> zzz345 12/19/1999 00:00:01 OAK asdas dasdasdasdasd
>
> I'm looking for the most efficient query to select the entire rows (or
> selected fields) based upon the id and the lated date. So the result
> would be:
> id time_moved moved_to blah blah ...
> abc123 01/17/2000 00:14:00 LAX ddedd dsdasda
> zzz123 01/17/2000 00:14:00 LAX ddedd dsdasda
> abc234 01/07/2000 00:14:00 LAX ddedd dsdasda
> zzz345 12/19/1999 00:00:01 OAK asdas dasdasdasdasd
>
> I realize that if I wanted only the id and time_moved, I could use
> select id,max(time_moved) from mytable group by id
>
> but I'm lookng for the most memory- and speed- efficient method of
> getting multiple columns (preferably the entire row). I'd rather not use
> cursors.
>
> Any ideas?
> thanks,
> Walter

[Lee Fesperman]

Walter Moore wrote:
> Well, I've found 2 methods of doing this in isql:
> select * from mytable group by id having move_by = max(move_by)

This one is non-standard (and non-deterministic) because the select list and having
clause reference columns not in the group by, without a set function.

> select * from mytable t1 where move_by in (select max(move_by) from
> mytable t2 where t1.id = t2.id group by id)

This is ok, but the group by is redundant. You can also use '=' instead of 'in'.

> The problem is, when I try to use Sybase's Java JDBC JConnect 3.1, the first
> method only returns the 1st row, while the second works correctly.

Sybase has traditionally supported this type of group by, but even they admit that it is
non-deterministic.

--
Lee Fesperman, FFE Software, Inc.