0

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
Improve this question
3
  • 1
    I don't know why expected is "10, 5/10/15, 3, google.com" instead of "10, 5/10/15, 10, google.com"? Commented May 14, 2015 at 1:40
  • 1
    Right. Why are you expecting 3 not 10 for the time ?. Commented May 14, 2015 at 1:47
  • You're correct, 3 should be 10. updating Commented May 14, 2015 at 2:03

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

This looks good so far, the one issue I'm running into actually is if a identity has two domains.. it throws the entire data for a loop. In example if identity 10 had went to yahoo.com and google.com the maxtime is no longer valid.
what the hell does your identity field mean if it can go to two websites? also, why is your time value separate to your date? the way it is means there's an issue with my query. If it was just a single datetime field, it would be as simple as the answer currently stands
The identity would be the unique identifier of the user. As one user could have viewed many websites. So the result would need to show a result for yahoo.com and google.com for the identity of 10.
I see. thanks. it wasn't clear from your sample data
The time field would represent the time spent on that specific website and the datetime would be currently recording when they first land on it.
|
1

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.

Improve this answer

1 Comment

Thanks for your response, I'll take a look into your solution also.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.