CODEWITHSUNDEEP
sqloracle11ggreatest-n-per-group
NoBullMan
NoBullMan

Reputation: 2184

Oracle query - select top records

Assuming the following table:

ID    Name    Revision
---   -----   --------
 1    blah     0
 2    yada     1
 3    blah     1
 4    yada     0
 5    blah     2
 6    blah     3

How do I get the two records, one for "blah" and one for "yada" with highest revision number (3 for blah and 1 for yada)? Something like:

ID    Name    Revision
---   -----   --------
 6    blah     3
 2    yada     1

Also, once these records are retrieved, how do I get the rest, ordered by name and revision?

I am trying to create a master-detail view where master records are latest revisions and details include the previous revisions.

Upvotes: 1

Views: 767

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Basically, with the aggregate function MAX():

SELECT "Name", MAX("Revision") AS max_revison
FROM   tbl
WHERE  "Name" IN ('blah', 'yada');
GROUP  BY "Name"
ORDER  BY "Name";   -- ordering by revision would be pointless;

If you need more columns from the row, there are several ways. One would be to join the above subquery back to the base table:

SELECT t.*
FROM (
   SELECT "Name", max("Revision") AS max_revison
   FROM   tbl
   WHERE  "Name" IN ('blah', 'yada');
   GROUP  BY "Name"
   ) AS sub
JOIN  tbl AS t ON t."Revision" = sub.max_revison
              AND t."Name" = sub."Name"
ORDER BY "Name";

Generally, this has the potential to yield more than one row per "Name" - if "Revision" is not unique (per "Name"). You would have to define how to pick one from a group of peers sharing the same maximum "Revision" - a tiebreaker.

Another way would be with NOT EXISTS, excluding rows that have greater peers, possibly faster:

SELECT t.*
FROM   tbl AS t
WHERE  "Name" IN ('blah', 'yada')
AND    NOT EXISTS (
   SELECT 1
   FROM   tbl AS t1
   WHERE  t1."Name" = t."Name"
   AND    t1."Revision" > t."Revision"
   )
ORDER  BY "Name";

Or you could use a CTE with an analytic function (window function):

WITH cte AS (
   SELECT *, ROW_NUMBER() OVER(PARTITION BY "Name" ORDER BY "Revision" DESC) AS rn
   FROM   tbl
   WHERE  "Name" IN ('blah', 'yada')
   )
SELECT *
FROM   cte
WHERE  rn = 1;

The last one is slightly different: one row per "Name" is guaranteed. If you don't use more ORDER BY items, an arbitrary row will be picked in case of a tie. If you want all peers use RANK() instead.

Upvotes: 5

Noel
Noel

Reputation: 10525

In Oracle, you can use LAST function to simplify this.

select max(id) keep (dense_rank last order by revision),
       name,
       max(revision)
from table
group by name;

Demo

Upvotes: 1

xagyg
xagyg

Reputation: 9711

This approach will select the rows for each Name with the maximum revision number for that Name. The result will be the exact output you were looking for in your post.

SELECT *
FROM tbl a
WHERE a.revision = (select max(revision) from tbl where name = a.name)
ORDER BY a.name

Upvotes: 2

Related Questions