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

) stts_id, stts_offset+stts_duration from table order by stts_id, stts_offset desc Marc Mamin From: pgsql-sql-ow...@postgresql.org pgsql-sql-ow...@postgresql.org on behalf of Gary Stainburn gary.stainb...@ringways.co.uk Sent: Thursday, July 25, 2013 10:57

Re: [SQL] delete where not in another table

) 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

); 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 related rows down to one On 25 May

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

Von: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org]quot; im Auftrag von quot;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

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

a join b ON ((a.a,a.b,a.c) IS DISTINCT FROM (b.a,b.b,b.c)) regards, Marc Mamin

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

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

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('public.' || $2

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: Re

[SQL] First aggregate with null support

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 WHEN

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 Andreas

[SQL] Controlling join order with parenthesis

: 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

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 the function

Re: [SQL] Ask To Optimize Looping

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');

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

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

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 this issue: insert

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

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

[SQL] varchar::timezone conversion

$ 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@postgresql.org) To make changes to your subscription: http

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

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, s.subtype, t.id_event_type, t.type

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 function to manage reference tables: HTH, Marc Mamin

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

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 pairs Hi, I have

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 values)

[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 yourRecordType AS ... DELARE previous_time int8;