469,080 Members | 1,747 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

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 ?
Jul 20 '05 #1
4 1359
"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
Jul 20 '05 #2
"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
Jul 20 '05 #3
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.
Jul 20 '05 #4
"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.

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by John Smith | last post: by
4 posts views Thread by Jonas Hei | last post: by
15 posts views Thread by Ant | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.