I've the following table.
comment postid userid tempid
c1 199 100003052455644 89
c2 193 100003052455644 2
c4 199 100003052455644 82
c5 198 100003052455644 5
c6 199 1097517748 65
Now I wanted to retrieve data in such a way that the data is first ordered in descending according to postid column then it is ordered in the order in which rows are inserted.Now when I retrieve the values in which postid is ordered by descending it gives me the result like this.
comment postid userid tempid
c6 199 1097517748 65
c4 199 100003052455644 69
c1 199 100003052455644 82
c5 198 100003052455644 5
c2 193 100003052455644 2
As you can see the userid who commented the last is appearing at first position.In the 199 postid.I came accross this problem by sorting the tempid column in the descending order also.But is there a way that i do not use the tempid column because this table will be filled very quickly and the auto-increment column will fill in quickly so I will be removing this tempid column in the future.So I thought it was not a good idea to do it like this is there any other solution for this ?
Right Now I am using the following query which is giving me the correct result.
SELECT comments.comment,comments.postid,user.name,comments.userid,comments.tempid
FROM
user
INNER JOIN comments ON user.userid=comments.userid
INNER JOIN posts ON posts.postID = comments.postid
WHERE
comments.postid <=
(SELECT MAX(postid) FROM
(
SELECT wall.postid FROM wall,posts WHERE
wall.postid = posts.postid AND posts.userid=?
ORDER BY wall.postid DESC LIMIT 10 OFFSET ?
)sq1
)
AND
comments.postid >=
(SELECT MIN(postid) FROM
(
SELECT wall.postid FROM wall,posts WHERE
wall.postid = posts.postid AND posts.userid=?
ORDER BY wall.postid DESC LIMIT 10 OFFSET ?
)sq2
)
AND
posts.userid = ?
ORDER BY comments.postid DESC,comments.tempid DESC;
Only the last line is of our concern.I want to achive the same result while removing the comments.tempid DESC clause . is it possible?
