How to sort sql result using a pre defined series of rows

Question

i have a table like this one:

--------------------------------
id | name
--------------------------------
1  | aa
2  | aa
3  | aa
4  | aa
5  | bb
6  | bb
... one million more ...

and i like to obtain an arbitrary number of rows in a pre defined sequence and the other rows ordered by their name. e.g. in another table i have a short sequence with 3 id's:

sequ_no | id     | pos 
-----------------------
1       |  3     |  0
1       |  1     |  1
1       |  2     |  2
2       |  65535 |  0
2       |  45    |  1
... one million more ...

sequence 1 defines the following series of id's: [ 3, 1, 2]. how to obtain the three rows of the first table in this order and the rest of the rows ordered by their name asc? how in PostgreSQL and how in mySQL? how would a solution look like in hql (hibernate query language)?

an idea i have is to first query and sort the rows which are defined in the sequence and than concat the other rows which are not in the sequence. but this involves tow queries, can it be done with one?

Update: The final result for the sample sequence 3, 1, 2 should look like this:

id | name
----------------------------------
3  | aa
1  | aa
2  | aa
4  | aa
5  | bb
6  | bb
... one million more ...

i need this query to create a pagination through a product table where part of the squence of products is a defined sequence and the rest of the products will be ordered by a clause i dont know yet.

Answer 1

One way: assign a position (e.g. 0) to each id that doesn't have a position yet, UNION the result with the second table, join the result with the first table, and ORDER BY seq_no, pos, name.

Answer 2

I'm not sure I understand the exact requirement, but won't this work:

SELECT ids.id, ids.name
FROM ids_table ids LEFT OUTER JOIN sequences_table seq
WHERE ids.id = seq.id
ORDER BY seq.sequ_no, seq.pos, ids.name, ids.id