[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] corresponding primary key for max(column)?
From:       Jason Pyeron <jason () pyeron ! com>
Date:       2002-12-24 0:12:06
[Download RAW message or body]


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 null
 ts     | timestamp with time zone |
 lat    | numeric(12,10)           | not null
 lon    | numeric(13,10)           | not null
 disp   | character varying(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 Data Point \
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-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 (2 rows)

Seq Scan on locations l0  (cost=0.00..22535105.55 rows=500 width=98)
  SubPlan
    ->  Materialize  (cost=22535.08..22535.08 rows=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.




---------------------------(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


[prev in list] [next in list] [prev in thread] [next in thread]