SQL - join up two separate sql queries

Question

I have a table that stores the page hits on a web application, storing ``` unique_row_id http_session_id page_name page_hit_timestamp ---------------------------------------------------------------- 0 123456789 index.html 2010-01-20 15:00:00 1 123456789 info.html 2010-01-20 15:00:05 2 123456789 faq.html 2010-01-20 15:00:15 3 987654321 index.html 2010-01-20 16:00:00 4 987654321 faq.html 2010-01-20 16:00:05 5 987654321 info.html 2010-01-20 16:00:15 6 111111111 index.html 2010-01-20 16:01:00 7 111111111 faq.html 2010-01-20 16:01:05 8 111111111 info.html 2010-01-20 16:01:15 ``` I want to run a sql query that will show me the most common page that users end browsing on. So my initial thinking is that in my (java) app, I can run a query that will select the distinct http_session_id values from the table, and then for each distinct http_session_id, run another query that gets the page with the 'latest' page_hit_timestamp, and sum a total for of all these pages. (For the sample data above, I'…

Accepted Answer

This should do what you want:

```
select 1.page_name, count(*) as ExitPageCount
from WebLog l
inner join (
    select http_session_id, max(page_hit_timestamp)
    from WebLog
    group by session
) lm on l.http_session_id = lm.http_session_id and l.page_hit_timestamp = lm.page_hit_timestamp
group by 1.page_name
```

SQL - join up two separate sql queries

Solution

Problem