Why not include another column that specifies the precise display sequence that you need and use ORDER BY on it?
On Tue, 15 May 2007 19:44:44 +0530, B V, Phanisekhar wrote: >>> 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] >----------------------------------------------------------------------------- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
