Selecting most recent row....

[sonny100]

Good evening guys,

I'm trying to work out a query that i've been having some trouble with all evening.

Basically, I have a table that contains info on messages between users on a site. All messages are relating to a particular conversation ID, or string. Users may have more than one message with a particular conversation ID/string.

So, here's what I need to select.....

I need to select the most recent entry with a particular conversation ID. So if the user has 6 entries with two seperate Con ID's, I only want to see the most recent one that he has made in both, so two results.

At the moment I'm just selecting all of the entries in the table for the user, but I want to limit this to showing only the most recent entry in each conversation string.

I'm not sure what further info anyone would need, but let me know what will help and i'll supply it!

Hope this makes sense!

[sonny100]

Been having more of a dig around, could this work?

PHP Code:

“SELECT * FROM ( SELECT string, MAX(time) as maxtime FROM message GROUP BY string) WHERE userid = $username”


[laserlight]

You could do something like this:

SELECT * FROM message WHERE userid='$username' ORDER BY time DESC LIMIT 1

Note that you may need to quote the identifier "time", and that you may need to use an alternative to LIMIT 1 if your database system does not support such SQL syntax.

[Roger Ramjet]

This one comes up fairly often and the answer is

PHP Code:

SELECT * FROM posts WHERE postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
WHERE t1.usedid = $userid AND sequence = 1
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE)


You can find an explanation of this method in my blog http://davidsoussan.co.uk

[Roger Ramjet]

Originally Posted by Roger Ramjet

This one comes up fairly often and the answer is

PHP Code:

SELECT * FROM posts WHERE postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
WHERE t1.usedid = $userid AND sequence = 1
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE)


You can find an explanation of this method in my blog http://davidsoussan.co.uk

Of course the more general solution is

PHP Code:

SELECT * FROM posts WHERE userid = $userid AND postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
WHERE sequence = 1
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE)


[laserlight]

Shouldn't "most recent" be determined by some kind of date/time column rather than id, since ids may not always be in order of the time when the entry was created?

[Roger Ramjet]

Autoincs are always in temporal order - by definition.

Of course you can use any column you want in the sequence join, just so long as it is unique within the required subset, integers just happen to be most convenient for this.

[laserlight]

Originally Posted by Roger Ramjet

Autoincs are always in temporal order - by definition.

Some database engines allow ids of deleted rows to be reused, with the exact rules depending on the configuration.

[Roger Ramjet]

But not MySQL. In any case, as I said, you can use any column of any type you like, just so long as it is unique within the set and can be ordered to suit your needs. If one has gone to all the trouble to configure the engine to fill in the gaps then I assume that one knows enough to spot this as well.

(The practice of reusing deleted autoinc IDs also smack of giving them a meaning outside their strict purpose of supplying a guaranteed unique key: ie. a meaning outside of the database. That is a practice to be frowned upon)

My solution is also a general purpose one in that it can be used to display the last post of multiple users in multiple threads, not just the one in question. All other methods will have difficulty in showing results from multiple subsets. In fact the only other way that this can be done that I know of is in Oracle where there is the vendor specific operator 'SEQUENCE' and this can be applied to a partition by using the operator 'OVER' so that it acheives the same result. Great if you are using Oracle but not much use to the rest of us.

[laserlight]

Originally Posted by Roger Ramjet

But not MySQL.

sonny100 did not state what database system was used, so MySQL is just an assumption, although it is probably the best assumption to make when you have no other information

Checking the MySQL documentation, can anyone confirm that AUTO_INCREMENT for MySQL does not allow reuse of ids even for deleted rows? The part about "this happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused" seems to hint otherwise.

Originally Posted by Roger Ramjet

(The practice of reusing deleted autoinc IDs also smack of giving them a meaning outside their strict purpose of supplying a guaranteed unique key: ie. a meaning outside of the database. That is a practice to be frowned upon)

I am not sure if such a meaning is implied, and in any case I note that the same can be said of using such ids to provide time information. In fact, that is why I brought it up, because I recall reading a database related book that recommended against such a practice.

[Roger Ramjet]

Originally Posted by laserlight

sonny100 did not state what database system was used, so MySQL is just an assumption, although it is probably the best assumption to make when you have no other information

Now laserlight you have been around here long enough to know that in this forum it is always mysql unless the user specifies otherwise.

That said, my method will work for any sql db engine: unless the user has done something like force the reuse of burnt autoincs.

Originally Posted by laserlight

Checking the MySQL documentation, can anyone confirm that AUTO_INCREMENT for MySQL does not allow reuse of ids even for deleted rows? The part about "this happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused" seems to hint otherwise.

That quote specifically relates to the use of an autoinc in a multi-column compound key. What it actually says is that the next autoinc value is then calculated as max(autoinc)+1. So the number will be reused only if you delete the last row, or of course if an insert fails. Gaps elsewhere in the sequence are NOT filled in EVER.

As to the rest, what you do inside the db is up to you. My strictures apply to the use of the column value in the outside world. I can't think of any other reason why one would be bothered about gaps in the sequence.

The book that you read probably also warned against using an autoinc as the primary key as well. Now that is a debate I am not going to waste my time on either.

But you are right. Striclty speaking the insert datetime should be used. The fact that no such column was mentioned does not mean that we can not assume it exists. After all I assumed that there was an autoinc column as well. The fact that datetime indexes are nmuch slower to process than integer indexes should not be important: except that query processing speed is almost always important to me at least.