How can I sort rows based on two columns in MySQL?

Question

I have a table that has a bunch of rows, but only three columns, date, username and posts.

Username has usernames, and they repeat a lot. Posts has a bunch of numbers. Date has the date something was posted in the Y-m-D format.

Now when I run SELECT * FROM table WHERE date = '2014-02-20', I get a bunch of mixed results, like this:

date                       username                    posts
2014-02-20                 user1                       1
2014-02-20                 user2                       2
2014-02-20                 user14                      1
2014-02-20                 user3                       1
2014-02-20                 user2                       3
2014-02-20                 user2                       4
2014-02-20                 user11                      1
2014-02-20                 user1                       2
2014-02-20                 user8                       2
2014-02-20                 user9                       3
2014-02-20                 user55                      4
2014-02-20                 user5                       3

I want to sort it out so it will look like this:

date                       username                    posts
2014-02-20                 user1                       1
2014-02-20                 user1                       2
2014-02-20                 user1                       3
2014-02-20                 user1                       4
2014-02-20                 user2                       1
2014-02-20                 user2                       2
2014-02-20                 user2                       3
2014-02-20                 user2                       4
2014-02-20                 user2                       5
2014-02-20                 user2                       6
2014-02-20                 user3                       1
2014-02-20                 user3                       2

How can I do that?

There's just a problem with both answers. user10 comes right after user1, user2 comes after user19, and so on.. — viniciusmunich - AssabetTech
– viniciusmunich - AssabetTech, Commented Feb 20, 2014 at 7:54

GautamD31 · Accepted Answer · 2014-02-20 07:44:10Z

1

Try like

SELECT * FROM table WHERE date = '2014-02-20' ORDER BY username,posts ASC

answered Feb 20, 2014 at 7:44

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

viniciusmunich - AssabetTech Over a year ago

I'll assign you the best answer because you answered in just 25 seconds, thanks.

Prasanth Bendra Over a year ago

I think user55 will come before user6 in this case

LefterisL · Accepted Answer · 2014-02-20 07:45:26Z

1

SELECT * FROM tablw WHERE date ='$something' ORDER BY username ASC, posts ASC

answered Feb 20, 2014 at 7:45

LefterisL

1,1534 gold badges18 silver badges37 bronze badges

4 Comments

Prasanth Bendra Over a year ago

I think user55 will come before user6 in this case

viniciusmunich - AssabetTech Over a year ago

@PrasanthBendra correct, I did that and user10 came right after user1.

Prasanth Bendra Over a year ago

And before user2 rite ?

viniciusmunich - AssabetTech Over a year ago

Yes, but that happened with both cases, ORDER BY username,posts ASC and ORDER BY username ASC, posts ASC.

Prasanth Bendra · Accepted Answer · 2014-02-20 08:02:31Z

0

Try this :

SELECT *,CONVERT(SUBSTRING(username, 5, 9),UNSIGNED INTEGER) AS userord FROM table WHERE date = '2014-02-20' ORDER BY userord ASC, posts ASC

answered Feb 20, 2014 at 8:02

Prasanth Bendra

33.2k9 gold badges56 silver badges74 bronze badges

Collectives™ on Stack Overflow

How can I sort rows based on two columns in MySQL?

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related