Re: [SQL] value from max row in group by

ution with distinct on: select distinct on (stts_id, stts_offset) stts_id, stts_offset+stts_duration from table order by stts_id, stts_offset desc Marc Mamin From: pgsql-sql-ow...@postgresql.org on behalf of Gary Stainburn Sent: Thursday, July 25, 2013 10:

Re: [SQL] delete where not in another table

1.user_id ) regards, Marc Mamin -- 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] reduce many loosely related rows down to one

lement ); regards, Marc Mamin > -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Torsten Grust > Sent: Dienstag, 28. Mai 2013 17:08 > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] reduce many loosely

Re: [SQL] Advice on re-writing a SELECT query.

n b ON ((a.a,a.b,a.c)<>(b.a,b.b,b.c)) but beware if null values are involved( 1<>NULL => NULL). In this case you can use : select * from a join b ON ((a.a,a.b,a.c) IS DISTINCT FROM (b.a,b.b,b.c)) regards, Marc Mamin

Re: [SQL] reduce many loosely related rows down to one

> > Von: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org]" im > Auftrag von "Bill MacArthur [webmas...@dhs-club.com] > Gesendet: Samstag, 25. Mai 2013 09:19 > An: pgsql-sql@postgresql.org > Betreff: [SQL] reduce many loosely related rows dow

Re: [SQL] Prevent double entries ... no simple unique index

same, but requires less space: create unique index on log(state) WHERE state IN (0,1); best regards, Marc Mamin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] checking for the existence of a current_setting ?

ay to make the check more smoothly, i.e. without relying on the exception ? maybe some undocumented internal function ? many thanks, Marc Mamin CREATE OR REPLACE FUNCTION public.var_get_check(int,text) RETURNS text AS $BODY$ BEGIN return current_setting('publ

Re: [SQL] conditional aggregates

something like ? Select min (case when X > 0 then X end) HTH, Marc Mamin From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Marcin Krawczyk Sent: Mittwoch, 8. Dezember 2010 14:20 To: Pavel Stehule Cc: pgsql-sql@postgresql.org Subject:

[SQL] First aggregate with null support

fault init condition of the aggregate :-( I also have a working one using an array function (first_wnull_a, below) , but I wonder if there is a simpler solution ... best regards, Marc Mamin CREATE OR REPLACE FUNCTION first_element_state(anyarray, anyelement) RETURNS anyarray AS $$ SELECT CASE

Re: [SQL] Overlapping Ranges- Query Alternative

or: Select Groups, generate_series FROM ranges JOIN generate_series(10,50,10) on ( ColumnA < generate_series) ORDER by Groups , generate_series ; regards, Marc Mamin From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andr

[SQL] Controlling join order with parenthesis

s=2400 width=4) Sort Key: t1.i -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) best regards, Marc Mamin -- 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] workaround for missing ROWNUM feature with the help of GUC variables

nately still using 8.3. sorry not to have mentioned that. Marc Mamin

[SQL] workaround for missing ROWNUM feature with the help of GUC variables

Hello, here my two pence on this recurring thema. (just a workaround) regards, Marc Mamin The PG parameter must be set to allow defining own configuration variables

Re: [SQL] record to columns: syntax question and strange behaviour

Hello, Your proposal unfortunately does not work if you try to query more than one value and want additional columns in the results, like in select column1,test(column1) FROM (values(1),(2)) foo cheers, Marc Mamin >IMO easiest would be to include a RETURNS SETOF record in

[SQL] record to columns: syntax question and strange behaviour

lumn1, (test(column1)).* FROM (values(1),(2)) foo => 1,2,3 2,3,4 NOTICE: done: 1 NOTICE: done: 1 NOTICE: done: 2 NOTICE: done: 2 Is there a way to avoid it ??? Thanks, Marc Mamin

Re: [SQL] Ask To Optimize Looping

be used is not readily predictable. Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join. " HTH, Marc Mamin

[SQL] select regexp_matches('a a a', '([a-z]) a','g');

ches the first 2 'a', only the trailing ' a' will be used to seek for further matching... Cheers, Marc Mamin -- 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] aggregation problem: first/last/count(*)

hives/68-More-Aggreg ate-Fun-Whos-on-First-and-Whos-on-Last.html But its is slightly slower as my solution. I'll still make a test with more data As I guess that swapping will grow fatser mith my query than with the first/last aggregate functions. cheers, Marc Mamin -Original

[SQL] aggregation problem: first/last/count(*)

set? This would allow to make a single scan of the table. something like select a_group, first(category) as first_category, last(category) as last_category, ... from test order by a_group,time Many thanks for any hints. Marc Mamin Here are some dummy values if you'd like to play with

Re: [SQL] Query how-to

this was silly from me! this should naturally look like this: select case when status ='Closed' then stop_date else start_date end as adate, sum(case when status ='Closed' then 1 else 0 end) as closedCount, sum(case when status ='New' then 1 else 0 end) as openedCount fro

Re: [SQL] Query how-to

Hi, What about something like that ? select adate, sum(openedCount) as openedCount, sum(closedCount) as closedCount from ( select sum(case when status ='Closed' then stop_date else start_date end) as adate, sum(case when status ='Closed' then 1 else 0 end) as closedCount sum(cas

[SQL] varchar::timezone conversion

(varchar,varchar) RETURNS int8 AS $body$ SELECT EXTRACT(EPOCH FROM $1 AT TIME ZONE $2)::int8*1000; $body$ LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Thanks for your help, Marc Mamin; -- Sent via pgsql-sql mailing list (pgsql-sql@postgres

Re: [SQL] exclude part of result

, p.b, p.c HTH, Marc Mamin -- 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] dynamic events categorization

ruct and at least part of the tests should happen on indexes only. If the query is run very often, you may want to add a boolean column is_past on show_date, and have a separate job that put the concerned records to true every x minutes ... HTH, Marc Mamin SELECT s.id_event_subtype,

Re: [SQL] SQL stored function inserting and returning data in a row.

> What about > $$ > INSERT INTO ; > select currval('seq_matchmaking_session_id'); > $$ language sql; > > ? Hello, I'm not sure that this would return the correct id in case of concurrent calls to your function. I'm using following kind of functio

Re: [SQL] SQL question: Highest column value of unique column pairs

order by date desc,name regards, Marc Mamin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Jenkins Sent: Saturday, January 12, 2008 1:10 AM To: pgsql-sql@postgresql.org Subject: [SQL] SQL question: Highest column value of unique column pai

Re: [SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

Hello Dirk, I have to disagree. Your first update query is very low. It probably implies to run the sub select statement for each row to be updated. Following update statement is already much faster: (using UPDATE FROM) update test_table set mygroup= t.mygroup from test_table as t

[SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

Hello, I have a large upddate to perform on tables which are dynamically generated (dynamic names). In this simplified example, the operation should replace in each family the "mygroup" of each item of rang=0 with the "mygroup" value of the element of rang=1 : (the * indicate the modified valu

[SQL] a way to generate functions dynamically ?

Hello, I need to generate some procedures that depend data models stored in my DBs. As I have different models in different databases, the stored procedures will differ. My idea is to generate the required stored procedures dynamically once a model is defined. I will probably do this within

Re: [SQL] Need to subtract values between previous and current row

I see 3 solutions. A) self join B) define a procedure that return a set of records. this use only a single table scan on the ordered table not tested, just the genera idea: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF AS ... DELARE previous_time int8; --(or whaever da