views:

15

answers:

1

Quick question, I have a table of messages each with a timestamp and a reply_id and a topic_id. Reply_ids can be the same as others, which means those messages are in the same group of replies. When I select a reply_id with the min timestamp, meaning it was the first message of that group, I would also like to know its numbered place within all results of that topic_id, ex. 3rd (3) 4th(4) etc. Anyone know how to do this or have any suggestions? Can this be done in pure sql?

SELECT reply_id,min(timestamp) as min FROM messages
   WHERE reply_chunk_id = ?
     AND topic_id = ?
+1  A: 

Using pure SQL:

SELECT m.reply_id,
       MIN(m.timestamp) as min,
       (SELECT COUNT(*)
          FROM MESSAGES t
         WHERE t.id <= m.id) AS rank
  FROM MESSAGES m
 WHERE m.reply_chunk_id = ?
  AND m.topic_id = ?

This works only if replyid is unique values. If there's a duplicate replyid before the replyid, then the COUNT will miss that.

Analytic functions, which MySQL doesn't support, would be a better option. You can recreate the functionality using variables in the MySQL SELECT statements.

OMG Ponies
reply_id is not unique but I do have a autoincremented column called id which can use, I will try this out and let you know, thanks ponies
Scarface
@Scarface: Updated in light of the news about the auto_increment `id` column.
OMG Ponies
seems to work thanks, really appreciate your help ponies, thanks for your time
Scarface