Merge Select statement and order dynamically?

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.

From stackoverflow

• 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

• 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.

• 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.

• 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

• 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;