select the record with the highest id value in sybase ase

Question

I have this query

    SELECT      Reg.id_Movimiento, Tip.NombreTipoMov
    FROM        ut_sgt_Movimientos_t Reg
    INNER JOIN  ut_sgt_TiposMovimientos_m Tip
                ON Reg.id_TipoMov = Tip.id_TipoMov
    WHERE       Reg.id_Registro = 367

With this output:

8 Retiro
6 Marcaje
7 Porteador
5 Descarga
1 Almacenaje

How can i select the record with the highest id value?

I can not use subquery, set rowcount or top 1, only agregate functions, having or group by

I have tried:

    SELECT      Reg.id_Movimiento, Tip.NombreTipoMov
    FROM        ut_sgt_Movimientos_t Reg
    INNER JOIN  ut_sgt_TiposMovimientos_m Tip
                ON Reg.id_TipoMov = Tip.id_TipoMov
    WHERE       Reg.id_Registro = 367
    HAVING      Reg.id_Movimiento = MAX(Reg.id_Movimiento)

But the output is

8 Retiro
8 Marcaje
8 Porteador
8 Descarga
8 Almacenaje

Answer 1

Well, since you specify what you cannot use, I'll assume everything else is fair game.

SET ROWCOUNT 1

SELECT      Reg.id_Movimiento, Tip.NombreTipoMov
FROM        ut_sgt_Movimientos_t Reg
INNER JOIN  ut_sgt_TiposMovimientos_m Tip
            ON Reg.id_TipoMov = Tip.id_TipoMov
WHERE       Reg.id_Registro = 367
ORDER BY Re.ID DESC

SET ROWCOUNT 0

Answer 2

I'm pretty sure the HAVING needs to be used with a Group By function.

SELECT      Reg.id_Movimiento, Tip.NombreTipoMov
FROM        ut_sgt_Movimientos_t Reg
INNER JOIN  ut_sgt_TiposMovimientos_m Tip
            ON Reg.id_TipoMov = Tip.id_TipoMov
WHERE       Reg.id_Registro = 367
GROUP FILTER rank() < 1
GROUP BY Reg.id_Movimiento, Tip.NombreTipoMov
GROUP ORDER BY Reg.id_Movimiento desc
HAVING      Reg.id_Movimiento = MAX(Reg.id_Movimiento)

Found a little more for Sybase