CODEWITHSUNDEEP
sql
F.Joodaki
F.Joodaki

Reputation: 151

How to get max value and using group by clause

I have a query like this:

select transactions_id,
    time_stamp,
    clock
from times
group by transactions_id
having sum(distinct type) = 1

now, I would like to get max value depending on id.

I used below queries but not worked:

select max(id),
    transactions_id,
    time_stamp,
    clock
from times
group by transactions_id
having sum(distinct type) = 1

or

select transactions_id,
    time_stamp,
    clock
from times
group by transactions_id
having sum(distinct type) = 1
    and max(id)

for example:

I have three conditions:

  1. type must be 1
  2. group by transactions_id
  3. max id

enter image description here

Upvotes: 0

Views: 44

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can find aggregates in one query and join its result with the table to get the relevant rows.

select *
from times t1
join (
    select transactions_id,
        max(id) as id
    from times
    where type = 1
    group by transactions_id
    ) t2 using (transactions_id, id);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If I understand correctly, you can use the ANSI standard row_number() function:

select t.*
from (select t.*,
             row_number() over (partition by transactions_id order by id desc) as seqnum
      from times t
     ) t
where seqnum = 1;

I am not sure what having sum(distinct type) = 1. That condition is not explained in the question.

Upvotes: 0

Related Questions