3
ID|  tag  |  version
-----+-----+-----
1|  A  |  10
2|  A  |  20
3|  B  |  99
3|  C  |  30
3|  F  |  40

desired output:

1 A 10
2 A 20
3 B 99

How can I get the max version of every ID and the corresponding tag for that version? Speed is important (I have around 28m rows) so a nested Select won't do it. Also a simple Group by ID with a max(version) doesn't work because I also need the corresponding Tag where the version is max.

Nickpick
  • 6,163
  • 16
  • 65
  • 116

2 Answers2

4

Use ROW_NUMBER() :

SELECT s.id,s.tag,s.version FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.version DESC) as rnk
   FROM YourTable t) s
WHERE s.rnk = 1
sagi
  • 40,026
  • 6
  • 59
  • 84
  • nested select will be very slow I assume if I have 28m rows – Nickpick Jul 11 '16 at 11:26
  • The nested select won't hurt the performance, its just so you'll be able to refer to `rnk` column which is evaluated in the inner query. Run the query and then decide about performace @nickpick – sagi Jul 11 '16 at 11:27
  • For some reason the output returns much less results than expected – Nickpick Jul 11 '16 at 13:02
  • That doesn't make sense.. this will return `1` row per `group` , meaning one row per `id` . @nickpick – sagi Jul 11 '16 at 13:31
  • 1 row per ID is what I'm expecting. The one with the highest version number. – Nickpick Jul 11 '16 at 13:32
  • And what do you get instead? This query should return exactly one row per `id` , without filtering any `id` . @nickpick – sagi Jul 11 '16 at 13:55
3

Try this

select id, max(tag) keep(dense_rank first order by VERSION desc) as tag, max(version) as version
from t group by id
Evgeniy K.
  • 1,137
  • 7
  • 11
  • why max(tag)? tag isn't a number. also "FROM keyword not found where expected" – Nickpick Jul 11 '16 at 11:25
  • It doesn't matter. Oracle looks for values in diapason 'first order by VERSION desc'. It finds sorted VERSION by desc(or max, it's the same) and return first founded row. Then Oracle gets Max(tag) of first returning row and because it is the single row then max works correctly. – Evgeniy K. Jul 11 '16 at 11:31
  • @Nickpick "FROM keyword not found where expected" - "from t " where t is your table. – Evgeniy K. Jul 11 '16 at 11:52
  • "because it is the single row then max works correctly": so it's not necessary to put max(tag)? – Nickpick Jul 11 '16 at 12:06
  • See link https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions. It is necessary because it is analytic function. Imagine situation '3| B | 99' and '3| C | 99'. On id=3 there are two values with max VERSION = 99 the you should use max(tag) – Evgeniy K. Jul 11 '16 at 14:19