Re: [SQL] how to get decimal to date form
On Wednesday 17 Sep 2003 13:50 in <[EMAIL PROTECTED]>, wireless ([EMAIL PROTECTED]) wrote: > For example where the rec_num is 30608124143.47069519725 the above > functions return 30608. Unless you are logging dates around 3 A.D., I suggest you add 2000 to the number before you apply the TO_CHAR() function. I strongly suspect your app is not Y2K-compliant. [I haven't written that for years!] -- Regards, Dave [RLU#314465] == [EMAIL PROTECTED] (David W Noon) Remove spam trap to reply via e-mail. == ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simple (?) join
On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: >create table orders ( >o_id serial primary key >... >); > >create table orders_log ( >ol_id serial primary key, >o_id int4 not null references orders(o_id), >ol_timestamp timestamp, >ol_user, >); > >How can I select all from orders and the last (latest) entry from the >orders_log? SELECT * FROM orders WHERE o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); No joins required. -- Regards, Dave [RLU #314465] ======= david.w.n...@ntlworld.com (David W Noon) === -- 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] simple (?) join
On Thu, 24 Sep 2009 16:15:07 -0400, justin wrote about Re: [SQL] simple (?) join: > > >David W Noon wrote:On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn >wrote about [SQL] simple (?) join: > > create table orders ( >o_id serial primary key >... >); > >create table orders_log ( >ol_id serial primary key, >o_id int4 not null references orders(o_id), >ol_timestamp timestamp, >ol_user, >); > >How can I select all from orders and the last (latest) entry from the >orders_log? > >SELECT * FROM orders >WHERE o_id IN (SELECT o_id FROM orders_log > WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); > >No joins required. > >I don't think that is what he is requesting. I read it he also wants >the timestamp included in the result set That shouldn't be too much of a problem. >A nested query > >Select >orders.*, >(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = > orders.oid) >From orders That won't give the desired results. I don't think the SQL parser will even accept it. >Still another option is using a join > >Select >orders.*, ol_timestamp >From orders >left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by > o_id) as JoinQuery on JoinQuery.o_id = orders.o_id That won't give the desired results either. If you change "left" to "inner" you will be closer though. Both of your queries will retrieve the entire orders table with a timestamp of some sort from the orders_log table. >The second one should be faster Try this for an elegant approach: WITH maxi AS (SELECT MAX(ol_timestamp) AS ts FROM orders_log) SELECT o.*, maxi.ts FROM orders AS o WHERE o.o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = maxi.ts); This caches the latest timestamp in a temporary table so that it can both appear in the result set and be used to constrain the selection from the orders_log table. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- 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] simple (?) join
On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple (?) join: [snip] >Quoting Gary >"How can I select all from orders and the last (latest) entry from the >orders_log?" In that case, a simple Cartesian product will do: SELECT o.*, maxi.ts FROM orders AS o, (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi; Since the cardinality of the subquery "maxi" is 1, it will give a result set with cardinality of the complete orders table. I don't understand why anybody would want to do that. [De gustibus ... ] -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- 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] simple (?) join
On Mon, 28 Sep 2009 12:02:20 +0100, Gary Stainburn wrote about Re: [SQL] simple (?) join: [snip] >For some reason the reply I sent on Friday didn't get through. >What I need is all of the order record and all of the latest log entry >returning as a join. Specifically I want for each order the most >recent log entry timestamp and it's associated user - i.e. who made >the the last log entry and when. In that case, try this: SELECT o.*, ol.ol_timestamp, ol.ol_user FROM orders AS o INNER JOIN orders_log AS ol ON ol.o_id = o.o_id WHERE ol.ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log AS ol2 WHERE ol2.o_id = o.o_id); This will omit orders that have never been logged. You have not specified what you want done with them. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- 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] foreign keys and lots of tables
On Wed, 18 May 2011 20:10:19 +0100, Gary Stainburn wrote about [SQL] foreign keys and lots of tables: >I have the following tables (individual seat allocation removed to >make it simpler) Omitting details makes the problem more difficult to comprehend. [snip] >create table booking_seats ( > b_id int4 not null references bookings(b_id), > c_id int4, -- carriage ID > c_seat varchar(10) -- seat label >); > >The following ensures the seat exists on the coach. (not shown) > >alter table booking_seats add constraint seat_exists >foreign key (c_id, c_seat) references coach_seats (c_id,c_seat); > >How would I ensure that the coach exists on the train. I would need to >convert the b_id to a t_id using the bookings table and I don't know >how. I think you will need to write a trigger procedure with something like the following query inside it: IF NOT EXISTS(SELECT * FROM train_coaches AS tc INNER JOIN bookings AS b ON b.t_id = tc.t_id WHERE b.b_id = NEW.b_id AND tc.c_id = NEW.c_id)) THEN -- Something is wrong. You cannot use a subquery in a CHECK constraint, so I think a trigger will be the go. >To complicate things, when the initial booking is made, bot c_id and >c_seat are NULL. Will this make any difference? If c_id is NULL you cannot check anything against it, so your data integrity has just gone for a Burton. -- Regards, Dave [RLU #314465] *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* dwn...@ntlworld.com (David W Noon) *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* signature.asc Description: PGP signature
