CODEWITHSUNDEEP
mysqlsqllimitforum
Doc
Doc

Reputation: 5266

how to get a single row for each user?

sorry for the title, i don't know how to explain it better...

i have a forum and i want to make a sort of achievement system in php

i want to know when users with posts>10 posted their 10th message...

the post table is like

post_id | post_date | userid | post_message | ...

i can get this result for each user with 

select userid, post_date from posts where userid=1 order by post_date limit 9,1

but i need a resultset like

id | date
id | date
id | date

it can only be done with procedures?

Upvotes: 1

Views: 75

Answers (2)

KaeL
KaeL

Reputation: 3659

try this one:

SELECT userid
    , SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(post_date ORDER BY post_date), ',', 10), ',', -1) AS PostDate
    FROM posts
    GROUP BY userid
    HAVING PostDate <> '' OR PostDate IS NOT NULL

But you need to pay attention with the maximum length that the GROUP_CONCAT can hold.

Upvotes: 1

Meherzad
Meherzad

Reputation: 8553

Try this query 

select 
    * 
from (
    select 
       @rn:=if(@prv=userid, @rn+1, 1) as rid, 
       @prv:=userid as userid,
       post_message
    from 
       tbl
    join 
       (select @rn:=0, @prv:=0) tmp
    order by 
       userid, 
       post_date) tmp 
where 
    rid=10

SQL FIDDLE

| RID | USERID | POST_MESSAGE |
-------------------------------
|  10 |      1 |       asdasd |
|  10 |      2 |       asdasd |

Upvotes: 1

Related Questions