CODEWITHSUNDEEP
sql-server
Gabu
Gabu

Reputation: 3

In clauses problems

i have been execute simple 3 sql query

SELECT * FROM Table where rowid in(1,2,3)
SELECT * FROM Table where rowid in(2,1,3)
SELECT * FROM Table where rowid in(3,2,1)

all Query return same result. means execute Query first in(1,2,3) so get result like

1) first record which rowid is 1

2) second record which rowid is 2

3) third record which rowid is 3

same like other Query.

If I'm calling second and third Query the result are same like first Query but I want get result by rowid calling sequence.

Upvotes: 0

Views: 38

Answers (2)

SWeko
SWeko

Reputation: 30892

When the server executes the query it basically for each row, determines whether it satisfies the where clause - so the where clause only determines if the row is in the result set, and not it's place.

Since the tables is actually a set, there is no intrinsic ordering of the rows (any order is supposed to be considered accidental), if you need ordered data you need to order it explicitly by the fields you want to sort, i.e. 

SELECT * 
FROM Table 
WHERE rowid in(2, 1, 3)
ORDER BY rowid

Upvotes: 0

Arion
Arion

Reputation: 31239

You need to ORDER BY:

SELECT * FROM Table where rowid in(1,2,3) ORDER BY rowid ASC
SELECT * FROM Table where rowid in(2,1,3) ORDER BY 
(
  CASE rowid 
    WHEN 2 
    THEN 1 
    WHEN 1 
    THEN 2 
    WHEN 3 
    THEN 3 
  END
)
SELECT * FROM Table where rowid in(3,2,1) ORDER BY rowid DESC

Upvotes: 1

Related Questions