CODEWITHSUNDEEP
sql
Leonar Bode
Leonar Bode

Reputation: 31

how do i select the row with the largest day in SQL

Hello I have this query to the database:

select DISTINCT(CO_GID),
max(last_modified ) as DIA,
TRX_INI_FREQUENCY as TRX
from C_BSC_TRX ,UTP_COMMON_OBJECTS 
where (OBJ_GID = CO_GID)  
and (CO_GID LIKE '114481%')
GROUP BY CO_GID,TRX_INI_FREQUENCY



CO_GID   DIA           TRX

114481  07/11/18       null 
114481  27/10/18        49
114481  22/06/20        96

the problem is that I only want the row to be returned with the most current date: in this case it would be 06-22-2020 and the group by prevents me because I have to put the TRX_INI_FREQUENCY in the group by

what I wanted to get would be:

CO_GID   DIA           TRX

114481  22/06/20        96

Upvotes: 1

Views: 50

Answers (3)

Olga Romantsova
Olga Romantsova

Reputation: 1081

select top 1 DISTINCT(CO_GID),
max(last_modified ) as DIA,
TRX_INI_FREQUENCY as TRX
from C_BSC_TRX ,UTP_COMMON_OBJECTS 
where (OBJ_GID = CO_GID)  
and (CO_GID LIKE '114481%')
GROUP BY CO_GID,TRX_INI_FREQUENCY
Order by DIA desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use keep to get the last value of trx:

select CO_GID,
       max(last_modified) as DIA,
       max(TRX_INI_FREQUENCY) keep (dense_rank first order by last_modified desc) as TRX
from C_BSC_TRX JOIN
    UTP_COMMON_OBJECTS 
    on OBJ_GID = CO_GID and CO_GID LIKE '114481%'
group by CO_GID;

Notes:

  • Never use commas in the FROM clause.
  • Always use proper, explicit, standard, readable JOIN syntax.
  • Qualify column names! It is quite unclear what columns come from which tables.

Upvotes: 1

Sean
Sean

Reputation: 1456

Depending on what you're using, Add SELECT TOP 1 and ORDER BY DIA DESC

Upvotes: 0

Related Questions