How do I select times from MySQL in order based on precedence?

Question

I know that question doesn't make much sense, but here goes:

Times Table

Authority           | Time
-------------------------------------
animuson@forums     | 45.6758
132075829385895     | 49.7869
qykumsoy@forums     | 44.45
439854390263565     | 50.761
user@forums         | 44.9
another@auth        | 46.123
bingo@nameo         | 47.4392

So let me explain this. By default, if you have not linked your account to the authority you use, it just stores times as the authority, but if you link your account, it stores your ID number instead. I want the people with ID numbers to have precedence, so they'll appear over someone who is not linked, but still in order. So for this sample of data, when choosing the top 5, it would output these results:

Authority           | Time
-------------------------------------
qykumsoy@forums     | 44.45
user@forums         | 44.9
animuson@forums     | 45.6758
132075829385895     | 49.7869
439854390263565     | 50.761
-------------------------------------
Ignoring These:
another@auth        | 46.123
bingo@nameo         | 47.4392

Even though those two users had better times, they got knocked off because they're not linked, the linked accounts got pushed up, but the top 5 still remained in order of their times. It is safe to assume that an '@' symbol being present within the Authority means that it is an unlinked account. It will always appear in an unlinked authority value and a linked account will always be pure numbers. Any ideas on how to do this in one query?

The current query I use which simply selects the top 5 without thinking:

SELECT * FROM `tronner_times` WHERE `mid` = '{$map['mid']}' ORDER BY `time` + 0 LIMIT 5

Answer 1

This is the first solution that comes to mind. I'm not sure if it can be optimized further, but you may want to try the following:

SELECT    dt.authority, dt.time 
FROM      (
             SELECT    authority, time 
             FROM      tronner_times 
             ORDER BY  INSTR(authority, '@') > 0, time 
             LIMIT 5
          ) dt
ORDER BY  dt.time;

Test case:

CREATE TABLE tronner_times (authority varchar(90), time decimal(8, 4));

INSERT INTO tronner_times VALUES ('animuson@forums', 45.6758);
INSERT INTO tronner_times VALUES ('132075829385895', 49.7869);
INSERT INTO tronner_times VALUES ('qykumsoy@forums', 44.45);
INSERT INTO tronner_times VALUES ('439854390263565', 50.761);
INSERT INTO tronner_times VALUES ('user@forums', 44.9);
INSERT INTO tronner_times VALUES ('another@auth', 46.123);
INSERT INTO tronner_times VALUES ('bingo@nameo ', 47.4392);

Result:

+-----------------+---------+
| authority       | time    |
+-----------------+---------+
| user@forums     | 44.9000 |
| another@auth    | 46.1230 |
| bingo@nameo     | 47.4392 |
| 132075829385895 | 49.7869 |
| 439854390263565 | 50.7610 |
+-----------------+---------+
5 rows in set (0.00 sec)

We are ordering twice, because the derived table returns the rows without the @ sign at the very top. The expression INSTR(authority, '@') > 0 returns 1 if the @ is present in the authority string, or 0 if it is not. Therefore the result set is first ordered by this expression, and then by the time field, giving rows without the @ a priority (since 0 is sorted before 1). We therefore order the 5 rows from the derived table by the time field to produce the expected final result.

Answer 2

My idea is to do a case statement to filter out numbers, since u say it is confirm that numbers means linked. I also noticed those with @forums are included, so this part should be easy with like %@forums. The link for examples for checking numbers are shown, but you will need to change a bit. 2nd link would seem easier to me.

SELECT * FROM `tronner_times` WHERE PATINDEX('%[0-9]%',mid) > 0 OR mid like '%@forums' ORDER BY `time` + 0 LIMIT 5

http://www.tek-tips.com/faqs.cfm?fid=6423

http://www.sqlservercurry.com/2008/04/how-to-check-if-string-contains-numbers.html