CODEWITHSUNDEEP
mysqlsql
Chris Favaloro
Chris Favaloro

Reputation: 79

MySQL Subquery / Query Issue

I'm having a mental block with this query, I'm trying to return the max date and the maximum time and do an order by of the identity. It would be greatly appreciate if someone can add a pair of eyes to this type of query So :

Data Set

Identity, Date, Time, Website
10, 5/10/15, 1,  google.com 
10,  5/10/15, 3, google.com
10, 5/10/15, 10, google.com 
25, 5/11/15, 1, yahoo.com
25, 5/11/15, 15, yahoo.com

Expected Result

10, 5/10/15, 10, google.com  
25, 5/11/15, 15, yahoo.com

Current Query

SELECT DISTINCT *, MAX(datetime) as maxdate, MAX(time), identity
FROM identity_track 
GROUP BY identity 
ORDER BY maxdate DESC

Upvotes: 0

Views: 38

Answers (2)

pala_
pala_

Reputation: 9010

Something like this?

select identity, max(date), max(time), website
  from identity_track
  group by website;

Demo here: http://sqlfiddle.com/#!9/5cadf/1

You can order by any of the fields you want.

Also, the expected output you posted doesn't line up with what it seems like you're attempting to do.

edit

Updated query based on additional information.

select t.identity, t.date, max(t.time), t.website
  from t
    inner join
     (select identity, website, max(date) d
       from t
       group by identity, website) q
    on t.identity = q.identity
      and t.website = q.website
      and q.d = t.date
   group by t.identity, t.website, t.date

This one should give you the users identity, the pages he visited, the last time he visited that page, and the most amount of time he spent in any visit on that last visit.

Upvotes: 1

Simon Darlow
Simon Darlow

Reputation: 550

Don't assume that all records for an identity are on the same day e.g. if the entity has times of 1/1/15 5pm and 1/2/15 2pm you'd get 1/2/15 5pm which is wrong.

I'd always merge the time and date but if you can't try this:

select t.identity, t.website, MAX(t.time)
FROM t
     INNER JOIN
           (
            select identity, max(date) as max_date
              from t
              group by identity;
            ) x
            ON t.identity = x.identity
            AND t.date = x.max_date
group by t.identity, t.website

Firstly we get the maximum date for each site. Then for that day, get the maximum time.

Hope this helps.

Upvotes: 1

Related Questions