0

below is the query i have.

select * from tblQuestionTable where Paper='HTML' and QuestionId in (select QuestionId from tblTestOverview where TestId=1)

The sub query gives an unsorted result set, but the after querying the second select the result is sorted. How can i get the result in the same order as the subquery.

Niar
  • 532
  • 2
  • 11
  • 23
  • 2
    Is there a suitable column in any of the tables to order by? You can't get the same "unordered" result guaranteed. – Martin Smith Oct 08 '13 at 09:18
  • i need to get the result in the same order as the subquery gives. is it possible? For order by we can use TestId – Niar Oct 08 '13 at 09:23
  • But your `WHERE` clause on `tblTestOverview` only returns rows where `TestId=1`. If all rows have the same testid that is useless for ordering. – Martin Smith Oct 08 '13 at 09:25
  • yup.silly me. there is only one column in the subquery result set. m afraid there is no such column to do order by:( – Niar Oct 08 '13 at 09:27
  • in tblQuestionTable we can use QuestionId column to do order by. But will the order by give the same unsorted result as that of the subquery? – Niar Oct 08 '13 at 09:37

2 Answers2

2

Any dataset your query is working with is by default unordered, whether it is a physical table or a derived one. Whatever order the server uses to read rows from it while actually executing the query is out of your control. That means you cannot reliably specify the order to be "same as in that subquery". Instead, why not just have a specific order in mind and specify it explicitly in the main query with an ORDER BY? For instance, like this:

SELECT *
FROM tblQuestionTable
WHERE Paper='HTML'
  AND QuestionId IN (SELECT QuestionId FROM tblTestOverview WHERE TestId=1)
ORDER BY QuestionId
;

Having said that, here's something that might be close to what you are looking for. The ROW_NUMBER function assigns row numbers to the derived dataset in an undetermined order (ORDER BY (SELECT 1)). It may or may not be the order in which the server has read the rows, but you can use the assigned values to order the final result set by:

SELECT q.*
FROM tblQuestionTable AS q
INNER JOIN (
  SELECT
    QuestionId,
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
  FROM tblTestOverview
  WHERE TestId = 1
) AS o
ON o.QuestionId = q.QuestionId
ORDER BY o.rn ASC
;
Andriy M
  • 76,112
  • 17
  • 94
  • 154
-1

select result for tblQuestionTable will be sorted based on its primary index by default unless specified. tblTestOverview select result also does the same. So you need to include the primary index key feild from tblTestOverview table in the select query for tblQuestionTable and specify an order by clause based on that field.

S Thomas
  • 42
  • 3
  • This is not the case. There is no default guaranteed sort order without `ORDER BY`. It can depend on index access methods, isolation level, degree of parallelism, and edition of SQL Server. – Martin Smith Oct 08 '13 at 09:26
  • So are you saying it will not always sort based on the primary key unless specified? – S Thomas Oct 08 '13 at 09:28
  • 1
    No. Primary Key isn't always clustered index to begin with. Even if the plan shows a clustered index scan this might be either ordered by key or [allocation ordered](http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/11/09/when-can-allocation-order-scans-be-used.aspx). If the advanced scanning feature of enterprise edition is used or a parallel plan then even if scanned in key order the results might not actually be returned in key order. – Martin Smith Oct 08 '13 at 09:31