In clauses problems

Question

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.

Of course they're all the same result - the sets aren't ordered, the queries are all identical — pala_, Jun 04 '15 at 09:59
http://stackoverflow.com/questions/866465/sql-order-by-the-in-value-list (the accepted answer is ANSI standard SQL) — , Jun 04 '15 at 10:37

score 1 · Answer 1 · answered Jun 04 '15 at 10:03

1

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

answered Jun 04 '15 at 10:03

Arion

31,011
10
70
88

the rowid will not static it become more then 3 or it could be different. – Gabu Jun 04 '15 at 10:08

score 0 · Answer 2 · answered Jun 04 '15 at 10:08

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

In clauses problems

2 Answers2