choosing a particular row from grouped rows

[sks]

I have a table which contains two strings and a date.

page_views
-----------------
sessionId
page
timestamp
This is added to each time someone views a page on our site. I want to
select the last page each user looked at, so I need to group on the
sessionId whilst getting it to select the one with the highest date
timestamp. Any help ?

[Virgil Green]

"sks" <sk******************@tekheadsdotcodotuk.co.uk> wrote in message
news:Td********************@eclipse.net.uk...

I have a table which contains two strings and a date.

page_views
-----------------
sessionId
page
timestamp
This is added to each time someone views a page on our site. I want to
select the last page each user looked at, so I need to group on the
sessionId whilst getting it to select the one with the highest date
timestamp. Any help ?

Something like...

select page from page_views where sessionID=<your value> order by timestemp
desc limit 1

if you're using MySQL

[Virgil Green]

"sks" <sk******************@tekheadsdotcodotuk.co.uk> wrote in message
news:Td********************@eclipse.net.uk...

I have a table which contains two strings and a date.

page_views
-----------------
sessionId
page
timestamp
This is added to each time someone views a page on our site. I want to
select the last page each user looked at, so I need to group on the
sessionId whilst getting it to select the one with the highest date
timestamp. Any help ?

Oh, you may have wanted a list for all users. My misunderstanding.

select * from page_views a where timestamp = (select max(b.timestamp) from
page_views b where a.sessionId=b.sessionId group by sessionId)

may do what you want depending upon what database you're using. It may also
be a bit slow.

- Virgil

[Bill Karwin]

Virgil Green wrote:

select * from page_views a where timestamp = (select max(b.timestamp) from
page_views b where a.sessionId=b.sessionId group by sessionId)

A couple of nitpicks:

1. 'group by sessionId' is unnecessary, because we know that each
execution of the subquery is restricted to one sessionId.

2. Subqueries work only in MySQL 4.1 and later, and sks didn't say which
version of MySQL he/she is using. I don't know how one could do this
type of query without using a correlated subquery.
Sks may have to do this in multiple queries:

select max(timestamp) as maxtimestamp, sessionId
from page_views group by sessionId

then loop through that result set:
select * from page_views
where sessionId = ? and timestamp = ?

Bill K.

[Virgil Green]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:ck*********@enews2.newsguy.com...

Virgil Green wrote:

select * from page_views a where timestamp = (select max(b.timestamp) from page_views b where a.sessionId=b.sessionId group by sessionId)
A couple of nitpicks:

1. 'group by sessionId' is unnecessary, because we know that each
execution of the subquery is restricted to one sessionId.

Habit... when using columnar functions, I almost always include the 'group
by' since it is almost always being used. Rarely to I use a columnar
function other than count() expecting to return a single row.
2. Subqueries work only in MySQL 4.1 and later, and sks didn't say which
version of MySQL he/she is using. I don't know how one could do this
type of query without using a correlated subquery.
And so sks should learn to post his/her version. In fact, we are only
assuming MySQL is being used because of the group in which it was posted.
I've seen enough posts where someone was simply seeking SQL help and decided
this forum would be the place to get it.
Sks may have to do this in multiple queries:

select max(timestamp) as maxtimestamp, sessionId
from page_views group by sessionId

then loop through that result set:
select * from page_views
where sessionId = ? and timestamp = ?

Bill K.