corresponding primary key for max(column)? - Mailing list pgsql-sql
From | Jason Pyeron |
---|---|
Subject | corresponding primary key for max(column)? |
Date | |
Msg-id | Pine.LNX.4.44.0212231858020.21628-100000@ns.pyerotechnics.com Whole thread Raw |
List | pgsql-sql |
is there a better way? for reasons not defined by me, i cannot constrain the data in the locations table with unique(ref,ts) that said Microsoft mappoint craps out if there are duplicate values in the ref column. so i need to create a view which returns locations.* for the row which contains the max(ts) group by ref. -jason pyeron assetdb=# \d locations Table "locations"Column | Type | Modifiers --------+--------------------------+------------------------------------------------------id | integer | not null default nextval('"locations_id_seq"'::text)ref | integer | not nullts | timestamp withtime zone |lat | numeric(12,10) | not nulllon | numeric(13,10) | not nulldisp | charactervarying(63) | Primary key: locations_pkey assetdb=# select * from locations ;id | ref | ts | lat | lon | disp ----+-----+-------------------------------+---------------+----------------+-------------------------------------- 1 | 1 | 2002-12-22 05:47:26.863774-05 | 39.3373066667 | -76.6245916667 | 0.00MPH 038.6 degrees 2 | 1 | 2002-12-22 05:52:57.211416-05| 39.3373066667 | -76.6245916667 | 0.00MPH 038.6 degrees 3 | 1 | 2002-12-22 05:54:18.125055-05 | 39.3373066667| -76.6245916667 | 0.00MPH 038.6 degrees 4 | 2 | 2002-12-22 06:04:48.348906-05 | 40.3373066667 | -76.2459166670| 0.00MPH 038.6 degrees 5 | 2 | 2002-12-22 06:04:48.348906-05 | 99.9999999999 | -99.9999999999 | Foo DataPoint 0.00MPH 038.6 degrees 6 | 1 | 2001-01-01 00:00:00-05 | 38.0000000000 | -76.9000000000 | dfdsfsd (6 rows) assetdb=# SELECT assetdb-# l0.id, l0.ref, l0.ts, l0.lat, l0.lon, l0.disp assetdb-# assetdb-# FROM assetdb-# locations l0 assetdb-# assetdb-# WHERE assetdb-# l0.id = assetdb-# ANY ( assetdb(# SELECT assetdb(# min(l1.id) AS max assetdb(# assetdb(# FROM assetdb(# ( assetdb(# SELECT assetdb(# l2.id, l2.ref, l2.ts, l2.lat, l2.lon, l2.disp assetdb(# assetdb(# FROM assetdb(# locations l2 assetdb(# assetdb(# WHERE assetdb(# l2.ts = assetdb(# ( assetdb(# SELECT assetdb(# max(l3.ts) AS max assetdb(# FROM assetdb(# locations l3 assetdb(# WHERE assetdb(# l3.ref = l2.ref assetdb(# ) assetdb(# ) as l1 assetdb(# assetdb(# GROUP BY assetdb(# l1.ref assetdb(# ) assetdb-# ;id | ref | ts | lat | lon | disp ----+-----+-------------------------------+---------------+----------------+----------------------- 3 | 1 | 2002-12-2205:54:18.125055-05 | 39.3373066667 | -76.6245916667 | 0.00MPH 038.6 degrees 4 | 2 | 2002-12-22 06:04:48.348906-05| 40.3373066667 | -76.2459166670 | 0.00MPH 038.6 degrees (2 rows) Seq Scan on locations l0 (cost=0.00..22535105.55 rows=500 width=98) SubPlan -> Materialize (cost=22535.08..22535.08rows=1 width=8) -> Aggregate (cost=22535.06..22535.08 rows=1 width=8) -> Group (cost=22535.06..22535.07 rows=5 width=8) -> Sort (cost=22535.06..22535.06 rows=5 width=8) -> Seq Scan on locations l2 (cost=0.00..22535.00 rows=5 width=8) SubPlan -> Aggregate (cost=22.51..22.51 rows=1 width=8) -> Seq Scan on locations l3 (cost=0.00..22.50 rows=5 width=8) -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron http://www.pyerotechnics.com - - Owner & Lead Pyerotechnics Development, Inc. - - +1 410 808 6646 (c) 500 West University Parkway #1S - - +1 410 467 2266 (f) Baltimore, Maryland 21210-3253 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited.