Re: [SQL] Add: Special sort querstion
I think it can not be done with default PostgreSQL functions and operators, because you must compare two different columns of two rows depending which row is on either side of the comparision. But I think you can do this with the following steps: 1) create a new type as a 4-tupel of start_lat, end_lat, start_lng, end_lng. 2) write a comparison function for this type 3) write a SQL-Function to convert 4 values into your new type (for example: ToMyType(start_lat, end_lat, start_lnd, end_lng) returns MyType...) 4) use ToMyType in the order clause of your select If this would work, I'm interested in a working example code :-) -- --- Dirk Jagdmann http://cubic.org/~doj - http://llg.cubic.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add: Special sort querstion
I think it can not be done with default PostgreSQL functions and operators, because you must compare two different columns of two rows depending which row is on either side of the comparision. But I think you can do this with the following steps: 1) create a new type as a 4-tupel of start_lat, end_lat, start_lng, end_lng. 2) write a comparison function for this type 3) write a SQL-Function to convert 4 values into your new type (for example: ToMyType(start_lat, end_lat, start_lnd, end_lng) returns MyType...) 4) use ToMyType in the order clause of your select If this would work, I'm interested in a working example code :-) -- --- Dirk Jagdmann http://cubic.org/~doj - http://llg.cubic.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add: Special sort querstion
On Fri, Mar 27, 2009 at 6:10 AM, Dominik Piekarski d.piekar...@vivawasser.de wrote: Oh, actually every row of the same id-range has the same start_lat/start_lng coordinates as the predecessors end_lat/end_lng coordinates. But the question remains the same. Is there a way to do something like ORDER BY (start_lat = end_lat AND start_lng = end_lng) ? Or maybe another way to achieve the same result? Would something like order by start_lat-endlat, start_lng-end_lng OR case when start_lat=end_lat AND start_lng=end_lng then 0 else 1 end ??? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Add: Special sort querstion
Oh, actually every row of the same id-range has the same
start_lat/start_lng coordinates as the predecessors end_lat/end_lng
coordinates. But the question remains the same. Is there a way to do
something like ORDER BY (start_lat = end_lat AND start_lng = end_lng) ?
Or maybe another way to achieve the same result?
Dominik Piekarski schrieb:
Hello everyone,
the following query
SELECT id, start_lat, start_lng, end_lat, end_lng
FROM strecken
WHERE ST_Intersects(strecke, geomfromtext('POLYGON((
52.5204252 13.3169317,
52.5407887 13.3169317,
52.5407887 13.3534097,
52.5204252 13.3534097,
52.5204252 13.3169317
))')) ORDER BY id;
produces by coincidence the following desired result:
idstart_latstart_lngend_latend_lng
id range1
18074 52.5212087 13.318111952.5226187 13.3183479
18075 52.5226187 13.318347952.5237154 13.3189702
18076 52.5237154 13.318970252.5244204 13.3201289
18077 52.5244204 13.320128952.5248120 13.3217812
id range2
18095 52.5195701 13.338185552.5204710 13.3390224
18096 52.5204710 13.339022452.5213653 13.3393443
18097 52.5213653 13.339344352.5219529 13.3395588
18098 52.5219529 13.339558852.5223511 13.3400846
id range3
20293 52.5408279 13.333883352.5404625 13.3350205
20294 52.5404625 13.335020552.5395881 13.3385825
20295 52.5395881 13.338582552.5387398 13.3447623
Its desired because every single row end_lat and end_lng has the same
value as the next row's start_lat and start_lng field (except last one).
I would like to achieve the same result without using id column inside
order by clause. Is that possible? How would that query look like?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
