Top row by group 2

[peterswan]

Hello,

Does anyone know how to select the top row of each group, and display all the records in the table for that row, without writing a subquery?

For example, my table has a date field and a location field, plus several other records. I want to run a query that gets all of the fields for the most recent date, per location.

This only gets the location and the date, but no extra fields:

select max(mc_date), mc_location
from csq_mc
group by mc_location

and if I add the extra fields to the SELECT statement, then ALL of the records are returned.

Can I get just the top date by location, plus the location, plus the other records?

Thanks,

Peter

 

[djbjr]

Try the following

SELECT c.*
FROM
csq_mc c,
(SELECT mc_location, MAX(mc_date) max_date
FROM csq_mc GROUP BY mc_location) m
WHERE c.mc_location = m.mc_location
AND c.mc_date = m.max_date;

RESULTS:

SQL>   create table csq_mc
  2      (mc_date date,
  3       mc_location varchar2(10),
  4       field1 varchar2(5),
  5       field2 varchar2(5),
  6      field3 varchar2(5))
  7  ;

Table created.

SQL> select * from csq_mc;

MC_DATE   MC_LOCATIO FIELD FIELD FIELD
--------- ---------- ----- ----- -----
01-JAN-00 LOCATION1  A     A     A
01-JAN-01 LOCATION1  B     B     B
01-JAN-02 LOCATION1  C     C     C
01-JAN-03 LOCATION1  D     D     D
01-JAN-04 LOCATION1  E     E     E
01-JAN-00 LOCATION2  F     F     F
01-JAN-01 LOCATION2  G     G     G
01-JAN-00 LOCATION3  H     H     H
01-JAN-01 LOCATION3  I     I     I

9 rows selected.

SQL> SELECT c.* 
  2  FROM 
  3  csq_mc c,
  4  (SELECT mc_location, MAX(mc_date) max_date
  5   FROM csq_mc GROUP BY mc_location) m
  6  WHERE c.mc_location = m.mc_location
  7   AND  c.mc_date     = m.max_date;

MC_DATE   MC_LOCATIO FIELD FIELD FIELD
--------- ---------- ----- ----- -----
01-JAN-04 LOCATION1  E     E     E
01-JAN-01 LOCATION2  G     G     G
01-JAN-01 LOCATION3  I     I     I

 

[peterswan]

very nice.

Thanks, djbjr.

It's sort of like creating a temp table, inserting the grouped records into the table, and then joining on the original table. However, I like how you don't have to go through all of the CREATE TABLE syntax associated with temp tables.

Is there a term for this type of query that you've given here?

Thanks,

Peter

 

[djbjr]

I believe that is simply called a sub-query.

 

[dbalearner]

In SQL Server or Sybase the code

SELECT mc_location, MAX(mc_date) max_date
5 FROM csq_mc GROUP BY mc_location) m

is a derived table. Derived table (m) is effectively like a temp table but only valid for the duration of query as opposed to temp tables that are valid for the whole session. In Oracle they are called in-line views.

 

[SantaMufasa]

Peter/DJBJr,

Not wanting to be nit-picky, just "nomenclaturly correct", for DJBJr's specific use, the technical term for the additional SELECT is an "in-line VIEW"; a sub-query, specifically, exists when the SELECT statement appears as an expression anywhere besides the FROM list.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[djbjr]

Thanks Dave, I was answering with my Monday brain.

It is a in-line view.

 

[djbjr]

and yes I know I should have said

an in-line view

 

[SantaMufasa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[taupirho]

And the more general case - select top N records for each group( e.g for N =3)

select t1.mc_date,t1.mc_location,t1.field1,t1.field2,t1.field3
from
(
select mc_date,mc_location,field1,field2,field3,
dense_rank() over(partition by mc_location order by mc_location,mc_date desc) rnk
from csq_mc) t1
where t1.rnk <= 3 <- substitute your N value here

/

MC_DATE MC_LOCATIO FIELD FIELD FIELD
--------- ---------- ----- ----- -----
01-JAN-04 LOCATION1 E E E
01-JAN-03 LOCATION1 D D D
01-JAN-02 LOCATION1 C C C
01-JAN-01 LOCATION2 G G G
01-JAN-00 LOCATION2 F F F
01-JAN-01 LOCATION3 I I I
01-JAN-00 LOCATION3 H H H