3

I have a list of IDs from Lucene Search engine and want to select these IDs from database in specific order (score).

Can I do that in SQL query? It seems like h2 doesn't know ORDER BY FIELD(id, <list,of,ids>) function - Function "FIELD" not found.

Thank you.

SELECT * FROM table WHERE id IN (3,1,2,4) ORDER BY FIELD(id, 3,1,2,4);
Xdg
  • 1,735
  • 2
  • 27
  • 42
  • I think my question is duplicating this: http://stackoverflow.com/questions/866465/sql-order-by-the-in-value-list . – Xdg Aug 27 '12 at 13:03

1 Answers1

2

Is this what you need?

SELECT * FROM table WHERE id IN (3,1,2,4) ORDER BY decode(id,3,1,1,2,2,3,4,4,5);
kothvandir
  • 2,111
  • 2
  • 19
  • 34
  • I think it isn't. It doesn't order anythink. ORDER BY FIELD is right function, but it doesn't exist in H2 (as far as I know). In MySQL it's okay to use it. – Xdg Aug 27 '12 at 12:48
  • I've edited my answer, sorry i didnt read your question correctly, I think the "DECODE" function will help. – kothvandir Aug 27 '12 at 12:59
  • There is no decode function in H2 (http://www.h2database.com/html/grammar.html). Sad, it seems I have to choose one of these solutions: http://stackoverflow.com/questions/866465/sql-order-by-the-in-value-list . – Xdg Aug 27 '12 at 13:09
  • Could you or @ThomasMueller provide an explanation to the decode syntax? The documentation is rather sparse: "This function is provided for Oracle compatibility (see there for details)." but doesn't provide a link to *what* Oracle compatibility. Is your example `DECODE` saying essentially `id == 3` => `order = 1`, `id == 1` => `order = 2`, 2 is 3, 4 is 4 and all others are 5? – Patrick M Oct 06 '14 at 18:04