Get max number Oracle SQL

Question

I have the following data in database, the primary key is the field SEQ, I wish to select the data which has the maximum seq:

ID     SEQ   FILE
1007   1     abc
1007   2     def

The following query is invalid but I wish to do the same thing as following.

SELECT * FROM table1 WHERE id = '1007' AND Max(seq)

Answer 1

select * from alber.table1;

MYID SEQ FILENAME

---

1007 2 abc
1007 10 def
1008 45 abc
1008 9 def

SELECT myid, seq, filename from alber.table1 mq where seq = (select max(seq) from alber.table1 sq where sq.myid = mq.myid);

MYID SEQ FILENAME

---

1007 10 def
1008 45 abc

Answer 2

Another approach:

select id,seq,"FILE" from 
(select t1.*,row_number() over (partition by id order by seq desc) cont_seq 
from your_table t1)
where cont_seq = 1
order by seq;

This will give you all rows grouped by id who has the max seq value. If you want an specific value just add the condition in the where clause like this:

select id,seq,"FILE" from 
(select t1.*,row_number() over (partition by id order by seq desc) cont_seq 
from your_table t1)
where cont_seq = 1 and id = '1007'
order by seq;

Answer 3

If I understood right, you wish something like this

select * 
from (select t.*, 
      max(t.seq) 
        keep (dense_rank first order by t.seq desc) 
        over (partition by t.id) max#
from table1 t) 
where seq = max#

Answer 4

SELECT id, seq, file
FROM ( 
   select id, seq, file, 
          max(seq) over (partition by id) as max_seq
   from table1 
   WHERE id = '1007'
) t 
where seq = max_seq;