pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder By Question
15+ messages / 13 participants
[nested] [flat]
* Order By Question
@ 2001-01-23 00:25 Tristan Colson <tristan_colson@pacbell.net>
0 siblings, 0 replies; 15+ messages in thread
From: Tristan Colson @ 2001-01-23 00:25 UTC (permalink / raw)
To: pgsql-sql
This seems like the answer must be pretty easy, but I can't think of it:
In the following statement:
select field1 from my_table where field2 in (3, 1, 2);
How can I modify this statement so that the record are returned in the
order of having field2 = 3, then field2 = 1, then field2 = 2.
As it stands, I am getting them returned in the order of the value of
field1.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Order By Question
@ 2001-01-23 01:01 Tristan Colson <tristan_colson@pacbell.net>
2001-01-23 01:20 ` Re: Order By Question Ian Harding <iharding@pakrat.com>
2001-01-24 07:51 ` Re: Order By Question Prasanth Kumar <kumar1@home.com>
0 siblings, 2 replies; 15+ messages in thread
From: Tristan Colson @ 2001-01-23 01:01 UTC (permalink / raw)
To: pgsql-sql
This seems like the answer must be pretty easy, but I can't think of it:
In the following statement:
select field1 from my_table where field2 in (3, 1, 2);
How can I modify this statement so that the record are returned in the
order of first those records having field2 = 3, then field2 = 1, then
field2 = 2.
As it stands, I am getting them returned in the order of the value of
field1.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Order By Question
2001-01-23 01:01 Order By Question Tristan Colson <tristan_colson@pacbell.net>
@ 2001-01-23 01:20 ` Ian Harding <iharding@pakrat.com>
1 sibling, 0 replies; 15+ messages in thread
From: Ian Harding @ 2001-01-23 01:20 UTC (permalink / raw)
To: pgsql-sql
Tristan Colson wrote:
> This seems like the answer must be pretty easy, but I can't think of it:
>
> In the following statement:
>
> select field1 from my_table where field2 in (3, 1, 2);
>
> How can I modify this statement so that the record are returned in the
> order of first those records having field2 = 3, then field2 = 1, then
> field2 = 2.
> As it stands, I am getting them returned in the order of the value of
> field1.
You can't =;^)
If you add field 2 to the select list and order by it, you can get them in
1,2,3 or 3,2,1 order.
Alternatively, you could add a case statement to your query to add a column
that would be used just to order the data. My syntax may be foobar but it
would be something like:
select field1,
CASE
WHEN field2 = 3 THEN 1
WHEN field2=1 THEN 2
WHEN field2=2 THEN 3
AS orderfield
from my_table
where field2 in (3,1,2)
order by orderfield;
- Ian
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Order By Question
2001-01-23 01:01 Order By Question Tristan Colson <tristan_colson@pacbell.net>
@ 2001-01-24 07:51 ` Prasanth Kumar <kumar1@home.com>
1 sibling, 0 replies; 15+ messages in thread
From: Prasanth Kumar @ 2001-01-24 07:51 UTC (permalink / raw)
To: pgsql-sql
<snip>
> This seems like the answer must be pretty easy, but I can't think of it:
>
> In the following statement:
>
> select field1 from my_table where field2 in (3, 1, 2);
>
> How can I modify this statement so that the record are returned in the
> order of first those records having field2 = 3, then field2 = 1, then
> field2 = 2.
> As it stands, I am getting them returned in the order of the value of
> field1.
>
<snip>
One way is to have a priority table where each value is mapped to its
associated priority and then you do a join against this table and order by
the priority value instead.
So you might have a table like follows:
priority value
1 3
2 1
3 2
--
Prasanth Kumar
kumar1@home.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* ORDER BY question
@ 2001-11-15 23:02 Charles Hauser <chauser@acpub.duke.edu>
2001-11-21 15:11 ` Re: ORDER BY question Masaru Sugawara <rk73@echna.ne.jp>
2001-11-22 01:39 ` Re: ORDER BY question Andrew G. Hammond <drew@xyzzy.dhs.org>
0 siblings, 2 replies; 15+ messages in thread
From: Charles Hauser @ 2001-11-15 23:02 UTC (permalink / raw)
To: pgsql-sql
Hope this is the correct forum - if not sorry.
I have a query which returns a value of the form: '20010822.1338.1'.
I would like to order the results, sorting :
1st: 20010822
2nd: 1338
3rd: 1
I can do this in perl, but have not found a way to do this using ORDER BY.
I am VERY new to DB's; biologist trying to do informatics.
Thanks.
--
Chuck
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: ORDER BY question
2001-11-15 23:02 ORDER BY question Charles Hauser <chauser@acpub.duke.edu>
@ 2001-11-21 15:11 ` Masaru Sugawara <rk73@echna.ne.jp>
1 sibling, 0 replies; 15+ messages in thread
From: Masaru Sugawara @ 2001-11-21 15:11 UTC (permalink / raw)
To: pgsql-sql
On Thu, 15 Nov 2001 18:02:04 -0500
Charles Hauser <chauser@acpub.duke.edu> wrote:
> Hope this is the correct forum - if not sorry.
>
> I have a query which returns a value of the form: '20010822.1338.1'.
>
> I would like to order the results, sorting :
>
> 1st: 20010822
> 2nd: 1338
> 3rd: 1
>
>
> I can do this in perl, but have not found a way to do this using ORDER BY.
Hi,
I'm supposing, for the sake of convenience, that a table including some
rows is defined, and modeling a query which has sequential numbers
(e.g. 1, 2, 3) to sort and SUBSTRINGs to divide the value into the three
parts. If your platform is PG, the query might goes well.
BTW, a part of "select * from tbl where id < 3" depends on your query.
create table tbl (id int4, tm text);
insert into tbl values(1, '20010822.1338.1');
insert into tbl values(2, '20011121.2152.3');
insert into tbl values(3, '20011222.1338.1');
-- on v7.1.2
select t.dt
from (select t1.id, 1 as rank, substring(t1.tm from 1 for 8) as dt
from (select * from tbl where id < 3) as t1
union all
select t2.id, 2 , substring(t2.tm from 10 for 4)
from (select * from tbl where id < 3) as t2
union all
select t3.id, 3 , substring(t3.tm from 15 for 1)
from (select * from tbl where id < 3) as t3
order by id, rank
) as t
dt
----------
20010822
1338
1
20011121
2152
3
(6 rows)
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: ORDER BY question
2001-11-15 23:02 ORDER BY question Charles Hauser <chauser@acpub.duke.edu>
@ 2001-11-22 01:39 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
1 sibling, 0 replies; 15+ messages in thread
From: Andrew G. Hammond @ 2001-11-22 01:39 UTC (permalink / raw)
To: Charles Hauser <chauser@acpub.duke.edu>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 2001 November 15 06:02 pm, Charles Hauser wrote:
> Hope this is the correct forum - if not sorry.
>
> I have a query which returns a value of the form: '20010822.1338.1'.
>
> I would like to order the results, sorting :
>
> 1st: 20010822
> 2nd: 1338
> 3rd: 1
>
>
> I can do this in perl, but have not found a way to do this using ORDER BY.
It looks like you're encoding date and then two numeric data types into a
single text string. Why? Not only is it inefficient from a storage
perspective, but it will limit the flexibility (and value) of your data in
the future. Solution? Redesign your database!
CREATE TABLE foo ( first DATE NOT NULL,
second INTEGER NOT NULL,
third INTEGER NOT NULL,
UNIQUE (first, second, third) );
The UNIQUE constraint implicitly creates an index on first, second and third,
which will be used to do the sorting in the following query:
SELECT * FROM foo ORDER BY first, second, third;
And for some backwards compatibility, how about a VIEW...
CREATE VIEW bar AS SELECT extract(year FROM first) ||
extract(month FROM first) || extract(day FROM first) ||
'.' || second || '.' third AS baz FROM foo;
- --
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iEYEARECAAYFAjv8VzcACgkQCT73CrRXhLG2eQCfVsrBJrPxLJABsG7Z1Zva7jZf
jWUAniScvuDkcqQVjyVCaeGhRIPzUPoV
=ZQ7e
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 15+ messages in thread
* order by question
@ 2005-03-09 12:41 Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-09 13:06 ` Re: order by question Richard Huxton <dev@archonet.com>
2005-03-09 13:48 ` Re: order by question Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-03-09 14:26 ` Re: order by question Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 15+ messages in thread
From: Gary Stainburn @ 2005-03-09 12:41 UTC (permalink / raw)
To: pgsql-sql
Hi folks.
I seem to remember somewhere being shown how to bump specific rows to
the top of a list; something along the lines of:
select c_id as key, c_des as value from customers order by c_id = 7,
c_id = 160, value;
however, although the statement is accepted the two rows specified are
not bumped to the top of the list, but instead appear in their correct
position in the order by value part.
Is it possible and if so how do I do it?
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: order by question
2005-03-09 12:41 order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-09 13:06 ` Richard Huxton <dev@archonet.com>
2005-03-09 14:06 ` Re: order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 1 reply; 15+ messages in thread
From: Richard Huxton @ 2005-03-09 13:06 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary Stainburn wrote:
> Hi folks.
>
> I seem to remember somewhere being shown how to bump specific rows to
> the top of a list; something along the lines of:
>
> select c_id as key, c_des as value from customers order by c_id = 7,
> c_id = 160, value;
Looks roughly right.
SELECT * FROM foo ORDER BY not(a=6),not(a=4),a;
a | b | c
---+------+-----
6 | ccc | BBB
4 | aaa | BBB
1 | aaa | AAA
2 | zxxx | AAA
3 | ccc | ZZZ
5 | zxxx | BBB
(6 rows)
Alternatively: (a<>6),(a<>4),a
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: order by question
2005-03-09 12:41 order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-09 13:06 ` Re: order by question Richard Huxton <dev@archonet.com>
@ 2005-03-09 14:06 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-09 15:50 ` Re: order by question Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Gary Stainburn @ 2005-03-09 14:06 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 09 March 2005 1:06 pm, you wrote:
> Gary Stainburn wrote:
> > Hi folks.
> >
> > I seem to remember somewhere being shown how to bump specific rows
> > to the top of a list; something along the lines of:
> >
> > select c_id as key, c_des as value from customers order by c_id =
> > 7, c_id = 160, value;
>
> Looks roughly right.
>
> SELECT * FROM foo ORDER BY not(a=6),not(a=4),a;
> a | b | c
> ---+------+-----
> 6 | ccc | BBB
> 4 | aaa | BBB
> 1 | aaa | AAA
> 2 | zxxx | AAA
> 3 | ccc | ZZZ
> 5 | zxxx | BBB
> (6 rows)
>
> Alternatively: (a<>6),(a<>4),a
Although this does exactly what I want, at first glance it should do
exactly the oposite.
I'm guessing that for each line it evaluates
not (a=6) 0 for true else 1
not (a=4) 0 for true else 1
everything else
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: order by question
2005-03-09 12:41 order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-09 13:06 ` Re: order by question Richard Huxton <dev@archonet.com>
2005-03-09 14:06 ` Re: order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-09 15:50 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 15+ messages in thread
From: Greg Stark @ 2005-03-09 15:50 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > Alternatively: (a<>6),(a<>4),a
>
> Although this does exactly what I want, at first glance it should do
> exactly the opposite.
>
> I'm guessing that for each line it evaluates
> not (a=6) 0 for true else 1
Not really, "not a=6" is an expression that evaluates to a boolean, true or
false. true sorts as "greater" than false. That order is counterintuitive but
it's because the default sort order is ascending. So the "lesser" false
records appear first.
If you put "not a=6" in your select column list you'll see the true and false
values appear.
--
greg
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: order by question
2005-03-09 12:41 order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-09 13:48 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 15+ messages in thread
From: Achilleus Mantzios @ 2005-03-09 13:48 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
O Gary Stainburn Ýãñáøå óôéò Mar 9, 2005 :
> Hi folks.
>
> I seem to remember somewhere being shown how to bump specific rows to
> the top of a list; something along the lines of:
>
> select c_id as key, c_des as value from customers order by c_id = 7,
> c_id = 160, value;
use the
case ... when .. then ... when ... then ... else ...
construct.
>
> however, although the statement is accepted the two rows specified are
> not bumped to the top of the list, but instead appear in their correct
> position in the order by value part.
>
> Is it possible and if so how do I do it?
>
--
-Achilleus
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: order by question
2005-03-09 12:41 order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-09 14:26 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2005-03-09 14:26 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Wed, Mar 09, 2005 at 12:41:55 +0000,
Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks.
>
> I seem to remember somewhere being shown how to bump specific rows to
> the top of a list; something along the lines of:
>
> select c_id as key, c_des as value from customers order by c_id = 7,
> c_id = 160, value;
>
> however, although the statement is accepted the two rows specified are
> not bumped to the top of the list, but instead appear in their correct
> position in the order by value part.
Are you sure? It looks like you are going to have them appear at the bottom
of the list doing the above. Remember that false sorts before true.
^ permalink raw reply [nested|flat] 15+ messages in thread
* ORDER BY question
@ 2006-05-11 12:00 Christian Paul Cosinas <cpc@cybees.com>
2006-05-11 03:55 ` Re: ORDER BY question Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 15+ messages in thread
From: Christian Paul Cosinas @ 2006-05-11 12:00 UTC (permalink / raw)
To: pgsql-sql
Hi,
For example I have a table like this
ID Name
1 A
1 B
2 C
2 D
And I have a Query like this:
SELECT * FROM table ORDER BY ID.
Would my result always give me the same order or is there may be a possible
different result?
For example?
1 B
1 A
2 D
2 C
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: ORDER BY question
2006-05-11 12:00 ORDER BY question Christian Paul Cosinas <cpc@cybees.com>
@ 2006-05-11 03:55 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 15+ messages in thread
From: Michael Glaesemann @ 2006-05-11 03:55 UTC (permalink / raw)
To: Christian Paul Cosinas <cpc@cybees.com>; +Cc: pgsql-sql
On May 11, 2006, at 21:00 , Christian Paul Cosinas wrote:
> For example I have a table like this
>
> ID Name
> 1 A
> 1 B
> 2 C
> 2 D
>
> And I have a Query like this:
> SELECT * FROM table ORDER BY ID.
>
> Would my result always give me the same order or is there may be a
> possible
> different result?
It may give you a different result: if the order is not explicitly
specified by the ORDER BY clause, you are not guaranteed to get the
rows back in any particular order. So for cases where you may not be
ordering on a unique column (such as above), you may want to do
something like:
SELECT *
FROM table
ORDER BY "ID", "Name";
Hope this helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2006-05-11 12:00 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-23 00:25 Order By Question Tristan Colson <tristan_colson@pacbell.net>
2001-01-23 01:01 Order By Question Tristan Colson <tristan_colson@pacbell.net>
2001-01-23 01:20 ` Ian Harding <iharding@pakrat.com>
2001-01-24 07:51 ` Prasanth Kumar <kumar1@home.com>
2001-11-15 23:02 ORDER BY question Charles Hauser <chauser@acpub.duke.edu>
2001-11-21 15:11 ` Re: ORDER BY question Masaru Sugawara <rk73@echna.ne.jp>
2001-11-22 01:39 ` Re: ORDER BY question Andrew G. Hammond <drew@xyzzy.dhs.org>
2005-03-09 12:41 order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-09 13:06 ` Re: order by question Richard Huxton <dev@archonet.com>
2005-03-09 14:06 ` Re: order by question Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-09 15:50 ` Re: order by question Greg Stark <gsstark@mit.edu>
2005-03-09 13:48 ` Re: order by question Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-03-09 14:26 ` Re: order by question Bruno Wolff III <bruno@wolff.to>
2006-05-11 12:00 ORDER BY question Christian Paul Cosinas <cpc@cybees.com>
2006-05-11 03:55 ` Re: ORDER BY question Michael Glaesemann <grzm@seespotcode.net>
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