Merge Select statement and order dynamically?

Question

1. I have a select statement which will return me 5 values as val1,val3,val5,val2,val4
2. I have another select statement which is going to return a set of records with one of the column having values from the above set (val1 to val5)

Is it possible to sort the second select statement with the result of the first select statement?

I mean in the order of val1,val3,val5,val2,val4.

This means if the first statement reverses the order I have to reverse the order.

Please let me know if this is possible.

Answer 1

You haven't posted your actual queries, so I may be assuming they are simpler than they are, but if your first statement was:

select val from valuetable order by someothercolumn

then your second query could be

select table2.name, table2.phonenumber, table2.creationdate, table2.val
from table2
left join valuetable on table2.val = valuetable.val
order by valuetable.someothercolumn

In other words, you could copy the ordering from your first statement to the second.

Answer 2

I'm not at all sure I understand your question, but I'll take a shot at it.

• Augment the first query with a row number (IIRC, MsSQL server 2005 has a ROW_NUMBER() function)
• Join on the val#, and sort by the associated row number
• Strip the row number from the result

Answer 3

Presumably, the first statement has some ordering on it (if it didn't, the order of the "val"s would be arbitrary, and you would'nt care about them.

So, take the second statement (that returns the data), and left outer to the first (that returns the ordered "val"s) on the vals, and order by whatever the first statement's order by is.

Answer 4

I'm not sure I fully understand the question, but try this. I assume your tables look like this?

Table1:
myfield1
val1
val2
val2

Table2:
myField2  myDataField
val1      test1
val2      test2
val3      test3

then your sql statement would look like this

SELECT myDataField 
FROM Table2 INNER JOIN Table1 ON Table2.myField2=Table1.myField1 
ORDER BY Table1.myField1 

HTH

Answer 5

Simple answer: Yes

...But you have to use a secondary ordering 
column and a nested inner select.

TableOne
  ValColumn
  +------+
  | val1 |
  +------+
  | val3 |
  +------+
  | val5 |
  +------+
  | val2 |
  +------+
  | val4 |
  +------+

SelectSetOne
   rank     ValColumn
  +--------+----------+
  | 1      |     val1 |
  +--------+----------+
  | 2      |     val3 |
  +--------+----------+
  | 3      |     val5 |
  +--------+----------+
  | 4      |     val2 |
  +--------+----------+
  | 5      |     val4 |
  +--------+----------+

TableTwo
    ValColumn   Col

  +-----------+------+
  | valN      | .... |
  +-----------+------+

Final Select
    rank    ValColumn   Col

  +--------+-----------+------+
  | 1      |     val1  | .... |
  +--------+-----------+------+
  | 1      |     ....  | .... |
  +--------+-----------+------+
  | 1      |     val1  | .... |
  +--------+-----------+------+
  | 2      |     val3  | .... |
  +--------+-----------+------+
  | 2      |     ....  | .... |
  +--------+-----------+------+
  | 2      |     val3  | .... |
  +--------+-----------+------+
  | 3      |     val5  | .... |
  +--------+-----------+------+
  | 3      |     ....  | .... |
  +--------+-----------+------+
  | 3      |     val5  | .... |
  +--------+-----------+------+
  | 4      |     val2  | .... |
  +--------+-----------+------+
  | 4      |     ....  | .... |
  +--------+-----------+------+
  | 4      |     val2  | .... |
  +--------+-----------+------+
  | 5      |     val4  | .... |
  +--------+-----------+------+
  | 5      |     ....  | .... |
  +--------+-----------+------+
  | 5      |     val4  | .... |
  +--------+-----------+------+

Here is the select statement:

SELECT
    SelectSetOne.rank,
    TableTwo.ValColumn,
    TableTwo.*
  FROM 
      (SELECT
         rank=count(*),
         ValColumn
       FROM TableOne) as SelectSetOne,
    INNER JOIN 
      TableTwo
    ON SelectSetOne.ValColumn = TableTwo.ValColumn
  ORDER BY
    SelectSetOne.rank;