>> Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)?
Actually I was getting the result (1, 2, 5, 6), but I wanted the result as per the given order in the OR clause "2 or 8 or 7 or 3". I wanted to know how sqlite works internally. >> Using which query we can get the result (1, 6, 5, 2)? Assume the values in the OR clause, be replaced by some subquery. Then in such scenarios how will I be able to maintain the order? I want the order of the subquery to be preserved in the main query. For e.g.: for the sub query returned values in order (2, 8, 7, 3), I need the main query should to return (1, 6, 5, 2) not (1, 2, 5, 6). Here (2, 8, 7, 3) is not fixed, which u have assumed in your reply. Regards, Phani -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 15, 2007 5:27 PM To: SQLite Subject: [sqlite] Re: Order of result of a query? B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Assume the database given below > > mainTable (rowid INTEGER, puid INTEGER) > > Assume main table be > > Rowid Puid > 1 2 > 2 3 > 3 4 > 4 6 > 5 7 > 6 8 > > "select rowid from mainTable where Puid = 2 OR puid = 8 OR puid = 7 > OR puid = 3" > > Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)? Why don't you try it and see for yourself? Note that, without ORDER BY clause, the order of records is an implementation detail you should not rely on. It may be (1, 6, 5, 2), or (1, 2, 5, 6), or something else. If you want a particular order, specify it explicitly. > Using which query we can get the result (1, 6, 5, 2)? select rowid from mainTable where Puid in (2, 8, 7, 3) order by (case Puid when 2 then 1 when 8 then 2 when 7 then 3 when 3 then 4 end); Igor Tandetnik ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
