CODEWITHSUNDEEP
sqloracle-database
chastain
chastain

Reputation: 83

SQL query for getting few things from one table

I have a problem with one table. This is how table looks:

+----------+------------+-------------+------+
| index_id | version_id |    date     | type |
+----------+------------+-------------+------+
|        1 |          4 | today       | C    |
|        1 |          2 | last month  | C    |
|        1 |          4 | today       | U    |
|        2 |          3 | yesterday   | c    |
|        2 |          4 | today       | C    |
|        2 |          2 | last year   | U    |
|        3 |          7 | yesterday   | C    |
|        3 |          6 | last month  | C    |
|        3 |          8 | today       | U    |
+----------+------------+-------------+------+

What I want to achieve is to get indexes (index_id) with max version and max date of both types.

Like this:

+----------+------------+-----------+------+
| index_id | version_id |   date    | type |
+----------+------------+-----------+------+
|        1 |          4 | today     | C    |
|        1 |          4 | today     | U    |
|        2 |          4 | today     | C    |
|        2 |          3 | yesterday | U    |
|        3 |          7 | yesterday | C    |
|        3 |          8 | today     | U    |
+----------+------------+-----------+------+

I want to ask you for some ideas for this problem. Thanks.

Upvotes: 1

Views: 69

Answers (3)

nobody
nobody

Reputation: 11080

Use row_number and partition to get the max in each group for a given index_id and type combination

select index_id,version_id,date,type from 
(
    select *, row_number() over (partition by index_id,type order by version_id,desc) as r_no
    from table
) a
where a.r_no = 1

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use subquery :

select t.*
from table t
where version_id = (select max(t1.version_id) 
                    from table t1 
                    where t1.index_id = t.index_id and 
                          t1.type = t.type 
                   );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

I would go for row_number():

select t.*
from (select t.*,
             row_number() over (partition by index_id, type order by version_id desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions