CODEWITHSUNDEEP
sql-server-2008
Niar
Niar

Reputation: 540

How to get unsorted result from a select query that uses an 'in'?

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.

Upvotes: 0

Views: 2138

Answers (2)

Andriy M
Andriy M

Reputation: 77717

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
;

Upvotes: 2

S Thomas
S Thomas

Reputation: 42

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.

Upvotes: -1

Related Questions