Обсуждение: ORDER records based on parameters in IN clause
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have a stmt where the outer-query is limited by the results of the inner query. I would like the outerquery to return records in the same order as the values provided in the IN clause (returned form the inner query). </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">The inner_query is returning id’s ordered by count(id) , i.e by most common occurrence.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">In essence,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">when I say </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">select * from table where id IN (2003,1342,799, 1450) </span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">I would like the records to be ordered as 2003, 1342, 799, 1450. The outer query has no knowledge ofthe count(id) that the inner_query is ordering by.</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Currently postgres returns it in this order (1450,1342,799,2003)</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Any help would be appreciated.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">R. Verghese</span></font></div>
On Mon, Jun 27, 2005 at 09:15:15AM -0700, Riya Verghese wrote: > > I have a stmt where the outer-query is limited by the results of the > inner query. I would like the outer query to return records in the same > order as the values provided in the IN clause (returned form the inner > query). If you want a particular order then use ORDER BY. The SQL standard says that without an ORDER BY clause, row order is implementation- dependent; the PostgreSQL documentation also says the same thing: http://www.postgresql.org/docs/8.0/static/queries-order.html "If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen." -- Michael Fuhr http://www.fuhr.org/~mfuhr/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > when I say > select * from table where id IN (2003,1342,799, 1450) > I would like the records to be ordered as 2003, 1342, 799, 1450. Just say: select * from table where id IN (2003,1342,799, 1450) ORDER BY id; If that doesn't work, you will have to be more specific and send us the exact query. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200506282010 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCwedPvJuQZxSWSsgRAsC0AKD2UrMtQJ6RRxbeZ8J2n68ewRt+EgCeN2UP Qttr1dX9soeBp5HxIp+vz/c= =cGiG -----END PGP SIGNATURE-----
>> when I say >> select * from table where id IN (2003,1342,799, 1450) >> I would like the records to be ordered as 2003, 1342, 799, 1450. >Just say: >select * from table where id IN (2003,1342,799, 1450) ORDER BY id; >If that doesn't work, you will have to be more specific and send us the exact query. Order by id will not do what you want, but this should. Select * from table where id = 2003; Union all Select * from table where id = 1342; Union all Select * from table where id = 799; Union all Select * from table where id = 1450;
On 6/27/05, Riya Verghese <riya.verghese@admissioncorp.com> wrote: > I have a stmt where the outer-query is limited by the results of the inner > query. I would like the outer query to return records in the same order as > the values provided in the IN clause (returned form the inner query). > > The inner_query is returning id's ordered by count(id) , i.e by most common > occurrence. > > In essence, > > when I say > > select * from table where id IN (2003,1342,799, 1450) > > Currently postgres returns it in this order (1450,1342,799,2003) Simplest, though not niciest solution would be: SELECT * FROM table WHERE id IN (2003,1342,799,1450) ORDER BY id = 2003 DESC, id = 1342 DESC, id = 799 DESC, id = 1450 DESC; You could write a function which will return position of interger inside integer[] array and use it as order key. :-) Regards, Dawid
On Wed, Jun 29, 2005 at 07:19:22AM -0400, Russell Simpkins wrote: > > Order by id will not do what you want, but this should. > Select * from table where id = 2003; > Union all > Select * from table where id = 1342; > Union all > Select * from table where id = 799; > Union all > Select * from table where id = 1450; Note that the semicolons should be omitted everywhere except for at the end of the entire query. Also, although the implementation might happen to return rows in that order, the documentation states that it's not guaranteed to: http://www.postgresql.org/docs/8.0/static/queries-union.html "UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned)." As the documentation states elsewhere and as the SQL standard says, without ORDER BY rows are returned in an unspecified order. The above query works by accident, not by design, so although it might work today there's no guarantee that it'll work tomorrow. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Riya Verghese wrote: > select * from table where id IN (2003,1342,799, 1450) > > I would like the records to be ordered as 2003, 1342, 799, 1450. The > outer query has no knowledge of the count(id) that the inner_query is > ordering by. I think this is the real problem: outer query must know count(id) to order by count(id). You can use it in the outer with something like this: SELECTtable.* FROMtableJOIN (SELECT id, count(id) AS count FROM... your subquery) AS x ORDER BYx.count Bye.
> SELECT > table.* > FROM > table > JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x > ORDER BY > x.count > > Bye. Sorry: I forgot join condition: SELECT table.* FROM table JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x ON (table.id = x.id) ORDER BY x.count
fair enough. but a simple order by id would never work. >From: Michael Fuhr <mike@fuhr.org> >To: Russell Simpkins <russellsimpkins@hotmail.com> >CC: pgsql-sql@postgresql.org >Subject: Re: [SQL] ORDER records based on parameters in IN clause >Date: Wed, 29 Jun 2005 05:57:23 -0600 > >On Wed, Jun 29, 2005 at 07:19:22AM -0400, Russell Simpkins wrote: > > > > Order by id will not do what you want, but this should. > > Select * from table where id = 2003; > > Union all > > Select * from table where id = 1342; > > Union all > > Select * from table where id = 799; > > Union all > > Select * from table where id = 1450; > >Note that the semicolons should be omitted everywhere except for >at the end of the entire query. Also, although the implementation >might happen to return rows in that order, the documentation states >that it's not guaranteed to: > >http://www.postgresql.org/docs/8.0/static/queries-union.html > >"UNION effectively appends the result of query2 to the result of >query1 (although there is no guarantee that this is the order in >which the rows are actually returned)." > >As the documentation states elsewhere and as the SQL standard says, >without ORDER BY rows are returned in an unspecified order. The >above query works by accident, not by design, so although it might >work today there's no guarantee that it'll work tomorrow. > >-- >Michael Fuhr >http://www.fuhr.org/~mfuhr/ > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, Jun 29, 2005 at 10:22:07AM -0400, Russell Simpkins wrote: > > fair enough. but a simple order by id would never work. I didn't mean to imply that it would -- I meant only that ORDER BY <something> is necessary to guarantee a particular row order. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote: > fair enough. but a simple order by id would never work. > Try this: select *,case when id=2003 then 1 when id=1342 then 2 when id=799 then 3 when id=1450 then 4 end as ob from tablename where id in (2003,1342,799,1450) order by ob;
Another option would be: SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; This should give you the results in the right order... - Michiel Scott Marlowe wrote: >On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote: > > >>fair enough. but a simple order by id would never work. >> >> >> > >Try this: > >select *, > case > when id=2003 then 1 > when id=1342 then 2 > when id=799 then 3 > when id=1450 then 4 > end as ob >from > tablename >where > id in (2003,1342,799,1450) >order by > ob; > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > > >
On 6/30/05, M.D.G. Lange <mlange@dltmedia.nl> wrote: > Another option would be: > SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; > This should give you the results in the right order... I don't think so... create temporary table seq as select * from generate_series(1,20) as g(id); select * from seq where id in (5,2,12);id ---- 2 512 select * from seq where id = 5 or id = 2 or id = 12;id ---- 2 512 It certainly doesn't work. You have to order it by something, like: select * from seq where id in(5,2,12) order by id=5 desc,id=2 desc,id=12 desc;id ---- 5 212 Regards, Dawid
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > fair enough. but a simple order by id would never work. That was me, sorry, I must have been asleep when I wrote it. :) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200506300636 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCw8uCvJuQZxSWSsgRAlVbAKCcJ9ktDZggHeICw/gZTBXoeAcK8gCghDKN 7jWWr2T1diDLeEmhzLhogCQ= =Yjrr -----END PGP SIGNATURE-----
M.D.G. Lange wrote: > Another option would be: > > SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; > > This should give you the results in the right order... > Per the SQL Standard, the rows of a table have no ordering. The result of a SELECT is just a derived table. Assuming a row order is *always* a bug. If you want an explicit row order you *must* use an ORDER BY clause. I would also recommend to you a saying that I learned many years ago, "Filter on the server, sort on the client." > - Michiel > > Scott Marlowe wrote: > >> On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote: >> >> >>> fair enough. but a simple order by id would never work. >>> >>> >> >> >> Try this: >> >> select *, >> case when id=2003 then 1 when id=1342 then 2 >> when id=799 then 3 when id=1450 then 4 end as ob >> from tablename where id in (2003,1342,799,1450) order by ob; >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> >> >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- Daryl Richter Platform Author & Director of Technology v: 610.361.1000 x202 (( Brandywine Asset Management ) ( "Expanding the Science of Global Investing" ) ( http://www.brandywine.com ))