CODEWITHSUNDEEP
sqlpostgresql
Eric T
Eric T

Reputation: 220

SQL Sub-queries and grouping

I should be able to do this but the brain appears to have melted down

Database is postgres

Table structure is simple, just four columns that matter:

Location, User, Activity, DateTime

I need to sort by location and output just the latest occurrence of each activity that has taken place. I don't know in advance what the activities are

Something like

Location    User    Activity   DateTime
London      Fred    A          08-29-2012
London      Fred    B          08-27-2012
Paris       John    A          08-29-2012
Tokyo       Fred    A          08-17-2012
Tokyo       Jane    D          08-29-2012

Thanks Folks

Upvotes: 2

Views: 165

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Fastest way in PostgreSQL is probably with DISTINCT ON:

SELECT DISTINCT ON (location, activity)
       location, activity, datetime, usr
FROM   tbl
ORDER  BY location, activity, datetime DESC, usr; -- usr only to break ties

Short form with positional parameters:

SELECT DISTINCT ON (1 ,2)
       location, activity, datetime, usr
FROM   tbl
ORDER  BY 1, 2, 3 DESC, 4;

This combines sorting and reducing to distinct rows in one operation. More details, explanation and benchmark in this related answer.

user is a reserved word. Don't actually use it as column name. I substituted with usr.

If performance should be crucial an index like the following will make the difference:

CREATE INDEX tbl_multi_idx ON tbl (location, activity, datetime DESC, usr);

Upvotes: 4

Nathan
Nathan

Reputation: 2775

select t1.location, t1.user, m.activity, m.datetime 
from table1 t1 inner join
(select activity, max(datetime) datetime from table1 group by activity) m
on t1.activity = m.activity and t1.datetime = m.datetime
order by t1.location asc

Upvotes: 0

Ariel
Ariel

Reputation: 26753

SELECT * FROM table JOIN (
  SELECT Activity, Location, Max(DateTime) DateTime FROM table
  GROUP BY Activity, Location
  ) m USING (Activity, Location, DateTime)

Best I can answer without fully understanding your question.

Upvotes: 2

Chains
Chains

Reputation: 13157

SELECT
   Location,
   User
   Activity
   DateTime
From
   myTable a
   INNER JOIN
   (SELECT Location, User, Max(DateTime) as DateTime FROM myTable GROUP BY Location, User) b
   ON a.Location = b.Location AND a.User = b.User AND a.DateTime = b.DateTime

Upvotes: 0

Related Questions