pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER records based on parameters in IN clause
16+ messages / 9 participants
[nested] [flat]
* ORDER records based on parameters in IN clause
@ 2005-06-27 16:15 Riya Verghese <riya.verghese@admissioncorp.com>
0 siblings, 4 replies; 16+ messages in thread
From: Riya Verghese @ 2005-06-27 16:15 UTC (permalink / raw)
To: pgsql-sql
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)
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.
Currently postgres returns it in this order (1450,1342,799,2003)
Any help would be appreciated.
Thanks,
R. Verghese
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-28 04:57 Michael Fuhr <mike@fuhr.org>
parent: Riya Verghese <riya.verghese@admissioncorp.com>
3 siblings, 0 replies; 16+ messages in thread
From: Michael Fuhr @ 2005-06-28 04:57 UTC (permalink / raw)
To: Riya Verghese <riya.verghese@admissioncorp.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 00:13 Greg Sabino Mullane <greg@turnstep.com>
parent: Riya Verghese <riya.verghese@admissioncorp.com>
3 siblings, 2 replies; 16+ messages in thread
From: Greg Sabino Mullane @ 2005-06-29 00:13 UTC (permalink / raw)
To: pgsql-sql; +Cc: riya.verghese@admissioncorp.com
-----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-----
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 11:19 Russell Simpkins <russellsimpkins@hotmail.com>
parent: Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 0 replies; 16+ messages in thread
From: Russell Simpkins @ 2005-06-29 11:19 UTC (permalink / raw)
To: pgsql-sql
>> 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;
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 11:44 Dawid Kuroczko <qnex42@gmail.com>
parent: Riya Verghese <riya.verghese@admissioncorp.com>
3 siblings, 0 replies; 16+ messages in thread
From: Dawid Kuroczko @ 2005-06-29 11:44 UTC (permalink / raw)
To: Riya Verghese <riya.verghese@admissioncorp.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 11:57 Michael Fuhr <mike@fuhr.org>
parent: Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 1 reply; 16+ messages in thread
From: Michael Fuhr @ 2005-06-29 11:57 UTC (permalink / raw)
To: Russell Simpkins <russellsimpkins@hotmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 12:09 Zac <zaccheob@inwind.it>
parent: Riya Verghese <riya.verghese@admissioncorp.com>
3 siblings, 1 reply; 16+ messages in thread
From: Zac @ 2005-06-29 12:09 UTC (permalink / raw)
To: pgsql-sql
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:
SELECT
table.*
FROM
table
JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x
ORDER BY
x.count
Bye.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 12:12 Zac <zaccheob@inwind.it>
parent: Zac <zaccheob@inwind.it>
0 siblings, 0 replies; 16+ messages in thread
From: Zac @ 2005-06-29 12:12 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 14:22 Russell Simpkins <russellsimpkins@hotmail.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 3 replies; 16+ messages in thread
From: Russell Simpkins @ 2005-06-29 14:22 UTC (permalink / raw)
To: mike@fuhr.org; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 14:33 Michael Fuhr <mike@fuhr.org>
parent: Russell Simpkins <russellsimpkins@hotmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Michael Fuhr @ 2005-06-29 14:33 UTC (permalink / raw)
To: Russell Simpkins <russellsimpkins@hotmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-29 15:07 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Russell Simpkins <russellsimpkins@hotmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2005-06-29 15:07 UTC (permalink / raw)
To: Russell Simpkins <russellsimpkins@hotmail.com>; +Cc: mike@fuhr.org; pgsql-sql
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;
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-30 07:34 M.D.G. Lange <mlange@dltmedia.nl>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 16+ messages in thread
From: M.D.G. Lange @ 2005-06-30 07:34 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-30 07:51 Dawid Kuroczko <qnex42@gmail.com>
parent: M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 0 replies; 16+ messages in thread
From: Dawid Kuroczko @ 2005-06-30 07:51 UTC (permalink / raw)
To: M.D.G. Lange <mlange@dltmedia.nl>; pgsql-sql
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
5
12
select * from seq where id = 5 or id = 2 or id = 12;
id
----
2
5
12
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
2
12
Regards,
Dawid
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-30 10:39 Greg Sabino Mullane <greg@turnstep.com>
parent: Russell Simpkins <russellsimpkins@hotmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Greg Sabino Mullane @ 2005-06-30 10:39 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: ORDER records based on parameters in IN clause
@ 2005-06-30 11:58 Daryl Richter <daryl@brandywine.com>
parent: M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 0 replies; 16+ messages in thread
From: Daryl Richter @ 2005-06-30 11:58 UTC (permalink / raw)
To: pgsql-sql
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 ))
^ permalink raw reply [nested|flat] 16+ messages in thread
* ORDER records based on parameters in IN clause
@ 2005-07-07 21:01 Riya Verghese <riya.verghese@admissioncorp.com>
0 siblings, 0 replies; 16+ messages in thread
From: Riya Verghese @ 2005-07-07 21:01 UTC (permalink / raw)
To: pgsql-sql
Thanks!
This worked beautifully.
Here's what I have:
SELECT a.listing_ id FROM cls.listings a
JOIN (SELECT listing_id,count(listing_id) AS count FROM cls.tagslistings
WHERE cust_id =27
AND tag_id IN (SELECT tag_id FROM cls.tags WHERE tag_name IN
('toys','263'))
GROUP BY listing_seq_id
) AS X ON X.listing_id= a.listing_id
ORDER by X.count DESC
R. Verghese
Author: Zac
Date: 2005-06-29 05:12
2005-06-29 12:12
-700
UTC
To: pgsql-sql
Subject: Re: [SQL] ORDER records based on parameters in IN clause
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2005-07-07 21:01 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-27 16:15 ORDER records based on parameters in IN clause Riya Verghese <riya.verghese@admissioncorp.com>
2005-06-28 04:57 ` Michael Fuhr <mike@fuhr.org>
2005-06-29 00:13 ` Greg Sabino Mullane <greg@turnstep.com>
2005-06-29 11:19 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-06-29 11:57 ` Michael Fuhr <mike@fuhr.org>
2005-06-29 14:22 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-06-29 14:33 ` Michael Fuhr <mike@fuhr.org>
2005-06-29 15:07 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-06-30 07:34 ` M.D.G. Lange <mlange@dltmedia.nl>
2005-06-30 07:51 ` Dawid Kuroczko <qnex42@gmail.com>
2005-06-30 11:58 ` Daryl Richter <daryl@brandywine.com>
2005-06-30 10:39 ` Greg Sabino Mullane <greg@turnstep.com>
2005-06-29 11:44 ` Dawid Kuroczko <qnex42@gmail.com>
2005-06-29 12:09 ` Zac <zaccheob@inwind.it>
2005-06-29 12:12 ` Zac <zaccheob@inwind.it>
2005-07-07 21:01 ORDER records based on parameters in IN clause Riya Verghese <riya.verghese@admissioncorp.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox