0

I'm new to SQL and I'm working on something to sort a table according to values in a second table so I have something like this 

SELECT id FROM (SELECT id, COUNT(*) FROM secondTable GROUP BY id ORDER BY COUNT(*) DESC) AS ordering;

and it's getting a column from the second table and orders it. Now I have a column like 7, 3, 8, 10, 4, 6 and I would like to sort another table based on those values so 7 would be ranked the highest. I looked into options and it seemed like something like this

SELECT * FROM firstTable ORDER BY FIELD(id, ordering);

would do what I want but I need to pass the column as individual arguments to FIELD(). Is there a way I can do it this way or some other way?

khstex
  • 21
  • 3
  • You could create a session number for you, unique to you, in a high concurrency system. Then insert those numbers into a worktable that has an auto increment (AI) in it. So let's say that worktable has 3 columns: AI, `sessionNum`, aNum. Now aNum is that list `7, 3, 8, 10, 4, 6` (but of course 1 row per aNum). Now all this is a simple join later using your `sessionNum` and order by AI – Drew Oct 13 '16 at 03:38
  • How one goes about getting their `sessionNum` is rather simplistic. See http://stackoverflow.com/a/38079598 . Once you are done with your use of it, housekeeping is easy against the worktable as you merely delete the rows with your sessionNum. So all of this is done with sets and joins and no `find_in_set` with CSV data. And it is fast as it uses indexes. – Drew Oct 13 '16 at 03:39
  • "*Now I have a column like 7, 3, 8, 10, 4, 6*" You have these values in a comma separated list in a single column in a single row? That sounds like a flawed design. Columns should only ever contain atomic values. – Michael - sqlbot Oct 13 '16 at 22:07

0 Answers0