CODEWITHSUNDEEP
phpmysql
Justine Smithies
Justine Smithies

Reputation: 69

how to get a count of an entry in mysql

________________________________
|oc_ct_forum_reply              |
--------------------------------|
| forum_reply_id | forum_post_id|
|-------------------------------|
|  21            |  25          |
|  22            |  25          |
|  25            |  25          |
|  28            |  25          |
---------------------------------

I have a table named oc_ct_forum_reply trying to find the first forum_reply_id with the following sql query:

  SELECT forum_reply_id
    FROM oc_ct_forum_reply
   WHERE forum_post_id = 25
ORDER BY reply_added
   LIMIT 1
       ;

i use another one to get the total number of replies for the forum_post_id:

  SELECT COUNT(*) AS total
    FROM oc_ct_forum_reply
   WHERE forum_post_id = 25
       ;

I need an sql query that gives me the position of a given forum_reply_id among the sorted forum_reply_ids belonging to a given forum_post_id. let's say i supply forum_post_id 25 and forum_reply_id 25, i want the query to return 3 as its the 3rd entry, or say i supply a forum_post_id of 25 and a forum_reply_id of 28, i wish to get the value 4 as its the fourth entry. I've tried all sorts of combinations using the two queries above.

Upvotes: 0

Views: 105

Answers (2)

Akash
Akash

Reputation: 5012

This should work

SELECT 
  t1.forum_reply_id,
  count(t2.forum_reply_id) as reply_number
FROM
  oc_ct_forum_reply t1
  JOIN oc_ct_forum_reply t2 ON ( t1.forum_reply_id >= t2.forum_reply_id AND t1.forum_post_id = t2.forum_post_id )
WHERE
  t1.forum_reply_id = 25
  AND t1.forum_post_id = 25
GROUP BY
  t1.forum_reply_id

SQLFIDDLE

Working

  1. oc_ct_forum_reply t1 gets the row having forum_reply_id = 25 AND t1.forum_post_id = 25

  2. Then, I do a SELF JOIN to get the current reply_id's number (by getting all the rows that have forum_reply_id less or equal to the current t1.forum_reply_id and which have the same forum_post_id of t1 and then grouping them

The above actions fetch 3 rows, as

t1.forum_reply_id , t2.forum_reply_id 
-----------------------------
 25               , 21                
 25               , 22                
 25               , 25

Finally, I just GROUP BY t1.forum_reply_id and use the COUNT() aggregate function to display the total rows present for the requested forum_reply_id ( 25 )

Upvotes: 4

Joe
Joe

Reputation: 1009

an alternative:

SELECT rownum FROM
(
SELECT @rownum:=@rownum+1 rownum, oc_ct_forum_reply.* 
FROM (SELECT @rownum:=0) r, oc_ct_forum_reply
) result_table
WHERE FORUM_REPLY_ID = 25;

http://sqlfiddle.com/#!2/5df2f/29

Upvotes: 0

Related Questions