MYSQL - Ordering query

Question

I am querying comments from my table.

But I cannot seem to get them in the correct order.

I would like to get the most recent first, which I am managing to do. But when I populate them in my tableView(ios app) the lastest comment is at the top. Which is correct.

What I need to do is get the latest comment like I am doing but I need the first(latest) at the bottom instead of at the top..

A bit like how instagram/facebook display there comments on the comment page.

I am not sure if I should re-arrange the comments once I have downloaded them or in the query it self.

This is how I am querying the comments.

SELECT * FROM 
    (SELECT 

    C.uuidPost,
    C.comment,
    C.type,
    C.date,
    C.uuid,
    USERS.id,
    USERS.username,
    USERS.profileImage
    FROM Activity C JOIN USERS ON
    C.id = USERS.id 
    WHERE C.type = 'comment'
    AND C.uuidPost = $uuidPost
    ORDER BY DATE DESC
    LIMIT 0, 15 ) x ORDER BY DATE

Thanks in advance to anyone that can help.

Is this a question about the SQL syntax? This doesn't seem to have anything to do with iOS, Swift, or UITableView. — rmaddy, Nov 11 '16 at 21:32
the most recent would mean that you want `DESC` not `ASC` ASC means ascending so the Oldest would be first not the most recent. — xQbert, Nov 11 '16 at 21:36
@rmaddy Well, I'm not sure because it could have something to do with how I order the comments recieved in ios? — , Nov 11 '16 at 21:36
If you think it might be a problem with your Swift code, then you should include relevant code in your question. Right now all you've done is post a SQL statement. No one can help you fix what you do with the results of that query if you don't provide relevant details. — rmaddy, Nov 11 '16 at 21:38
@rmaddy I shall add some code if necessary. But all I am doing is inserting the result I get in the tableView. And as normal the first result gets added to the top of the tableview. Not sure if there is a way to change that order in IOS but if not then I think I have to do so in my query. Do you know how to re-arrange in swift by any chance? — , Nov 11 '16 at 21:42
@xQbert yes I know, sorry in my offical code I have DESC but what I need to do is populate my tableView in ios with the first result from the query to the bottom. But as normal it inserts it to the top of the tableView. Is there a way to re-arrange the mysql query to have the latest at the bottom? — , Nov 11 '16 at 21:43
I think an example would be worth 100 words here.. Either you want DESC or you want ASC but it seems like there's something else that I just don't understand. update your question with sample data and expected output. — xQbert, Nov 11 '16 at 21:48
@xQbert Well I think ASC is probably what I want but I need to skip through the count(i.e 30 comments) to get the latest 15. So ASC would give me comment's 15-30, but I need to get 1-15 in ASC order...Will try to get something together...But I am trying to get the same order as Instagram gets there comments or facebook.... — , Nov 11 '16 at 21:51
I think I get it now... So in a set of 100 records you want to sort DESC so the newest is first, but then you want to display the records 85-100 FIRST; that right? and now my brain is officially FRI-dayd' — xQbert, Nov 11 '16 at 21:53
After you load the data try to scoll the tableview to the bottom — ELKA, Nov 12 '16 at 05:21
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query and I might show you — Strawberry, Nov 12 '16 at 16:41
Oh, right - well that's really easy: http://sqlfiddle.com/#!2/6f7bf/8 — Strawberry, Nov 12 '16 at 17:31

score 1 · Answer 1 · answered Nov 11 '16 at 21:36

1

Did you try:

ORDER BY DATE DESC

This will change your sort to DESCending order instead of ASCending order

Also (unrelated)... I live to avoid using "Date" as a column name and prefer to use a word that will never be a reserved word in any programming language... for example "comment_date" or "commentDate"

answered Nov 11 '16 at 21:36

scott

85
7

Yes correct I actually have DESC in my original code. But my problem is that I need the top/first comment at the bottom of my tableview not at the top – Nov 11 '16 at 21:39

score 1 · Answer 2 · answered Nov 12 '16 at 05:58

1

Not sure if this is what you want:

You start with the tableView at the bottom(latest message) and when you scroll up you will get older messages.

Keep your query in ascending order. So that the first message in the result array is the oldest.

After you load the messages sorted and call tableView.reloadData(), you can scroll to the bottom

var messages: [String] = [String](count: 100, repeatedValue: "")

override func viewDidLoad() {
    super.viewDidLoad()
    for i in 0..<100{ //message 0 is the oldest
        messages[i] = "Message \(i)"
    }
    tableView.reloadData()
}

override func viewWillAppear(animated: Bool) {
    if (self.messages.count > 0){
        self.tableView.scrollToRowAtIndexPath(NSIndexPath(forRow: self.messages.count-1, inSection: 0), atScrollPosition: .Bottom, animated: false)
    }
}

answered Nov 12 '16 at 05:58

ELKA

735
6
17

Seems to be like what I want. but I am only querying 15 from the server in ASC , so I will get comments 1-15 when I need comment 85-100 not sure if thats clear. – Nov 12 '16 at 08:12
Not an SQL expert but the problem could be with the naming of the column "DATE". Try to use C.Date or rename it. [Check Here For An example](http://stackoverflow.com/questions/9511882/sorting-by-date-time-in-descending-order) – ELKA Nov 12 '16 at 11:16
Will do but I don't think that will change anything...Will let you know anyway – Nov 12 '16 at 12:16

score 0 · Accepted Answer · answered Nov 13 '16 at 15:01

First you need to order them by DATE DESC and then add this to the begining of the query SELECT * FROM ( and this to the end to close the brackets ) x ORDER BY DATE that will invert the result and present the most recent at the bottom now instead of at the top.

SELECT * FROM 
    (SELECT 

    C.uuidPost,
    C.comment,
    C.type,
    C.date,
    C.uuid,
    USERS.id,
    USERS.username,
    USERS.profileImage
    FROM Activity C JOIN USERS ON
    C.id = USERS.id 
    WHERE C.type = 'comment'
    AND C.uuidPost = $uuidPost
    ORDER BY DATE DESC
    LIMIT 0, 15 ) x ORDER BY DATE

Hope it helps.

MYSQL - Ordering query

3 Answers3