limiting mysql results by range of a specific key INCLUDING DUPLICATES
- by aVC
I have a query
SELECT p.*, m.*,
(SELECT COUNT(*) FROM newPhotoonAlert n WHERE n.userIDfor='$id' AND n.threadID=p.threadID and n.seen='0') AS unReadCount
FROM posts p
JOIN myMembers m ON m.id = p.user_id
LEFT JOIN following f
ON (p.user_id = f.user_id AND f.follower_id='$id'
AND f.request='0' AND f.status='1')
JOIN myMembers searcher ON searcher.id = '$id'
WHERE ((f.follower_id = searcher.id) OR m.id='$id')
AND p.flagged <'5'
ORDER BY p.threadID DESC,p.positionID
It brings result as expected but I want to add Another CLAUSE to limit the results.
Say a sample (minimal shown) set of data looks like this with the above query.
threadID postID positionID url
564 1254 2 a.com
564 1245 1 a1.com
541 1215 3 b1.com
541 1212 2 b2.com
541 1210 1 b3.com
523 745 1 c1.com
435 689 2 d2.com
435 688 1 a4.com
256 345 1 s3.com
164 316 1 f1.com
.
.
I want to get ROWS corresponding to 2 DISTINCT threadIDs starting from MAX, but I want to include duplicates as well.
Something like
AND p.threadID IN (Select just Two of all threadIDs currently selected, but include duplicate rows)
So my result should be
threadID postID positionID url
564 1254 2 a.com
564 1245 1 a1.com
541 1215 3 b1.com
541 1212 2 b2.com
541 1210 1 b3.com