CODEWITHSUNDEEP
mysqlsortingsql-order-by
Dirk Paul
Dirk Paul

Reputation: 137

Select value from table sorted by a certain order from another table

I want to select value from table sorted by a certain order.

I have a table called test that looks like this:

| date     | code      | value    |
+----------+-----------+----------+
| 20050104 | 000005.SZ | -6359.19 |
| 20050104 | 600601.SH | -7876.34 |
| 20050104 | 600602.SH | -25693.3 |
| 20050104 | 600651.SH |   NULL   |
| 20050104 | 600652.SH | -15309.9 |
...
| 20050105 | 000005.SZ | -4276.28 |
| 20050105 | 600601.SH | -3214.56 |
...
| 20170405 | 000005.SZ | 23978.13 |
| 20170405 | 600601.SH | 32212.54 |

Right now I want to select only one date, say date = 20050104, and then sort the data by a certain order (the order that each stock was listed in the stock market).

I have another table called stock_code which stores the correct order:

+---------+-----------+
| code_id | code      |
+---------+-----------+
|       1 | 000002.SZ |
|       2 | 000004.SZ |
|       3 | 600656.SH |
|       4 | 600651.SH |
|       5 | 600652.SH |
|       6 | 600653.SH |
|       7 | 600654.SH |
|       8 | 600602.SH |
|       9 | 600601.SH |
|      10 | 000005.SZ |
...

I want to sorted the selected data by stock_code(code_id), but I don't want to use join because it takes too much time. Any thoughts?

I tried to use field but it gives me an error, please tell me how to correct it or give me an even better idea.

select * from test 
    where date = 20050104 and code in (select code from stock_code order by code)
    order by field(code, (select code from stock_code order by code));

Error Code: 1242. Subquery returns more than 1 row

Upvotes: 0

Views: 1050

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

You told us that you don't want to join because it takes too much time, but the following join query is probably the best option here:

SELECT t.*
FROM test t
INNER JOIN stock_code sc
    ON t.code = sc.code
WHERE t.date = '20050104'
ORDER BY sc.code_id

If this really runs slowly, then you should check to make sure you have indices setup on the appropriate columns. In this case, indices on the code columns from both tables as well as an index on test.date should be very helpful.

ALTER TABLE test ADD INDEX code_idx (code)
ALTER TABLE test ADD INDEX date_idx (date)
ALTER TABLE code ADD INDEX code_idx (code)

Upvotes: 2

Related Questions