- I have a select statement which will return me 5 values as val1,val3,val5,val2,val4
- 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.
-
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 someothercolumnthen 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.someothercolumnIn 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 test3then your sql statement would look like this
SELECT myDataField FROM Table2 INNER JOIN Table1 ON Table2.myField2=Table1.myField1 ORDER BY Table1.myField1HTH
-
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;
