Re: [SQL] must appear in GROUP by clause issue

) GROUP BY name ORDER BY name; I would also inspect the subquery, ordered by name, to see if anything weird leaps out at you in terms of the adjacent rows that *should* be grouped. As an aside, I replaced the (x = 0 OR x IS NULL) and converse constructions with COALESCE() functions, which IMO are

Re: [SQL] sub query and AS

descr from (select distinct freephone from calendario order by 1) as a ) as b; sql := $$select * FROM crosstab('select uscita,freephone,id from calendario order by 1','select distinct freephone from calendario order by 1&#

[SQL] Fill array with series

y_agg - {5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23} Thanks, --Lee -- Lee Hachadoorian PhD, Earth & Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu/

Re: [SQL] COPY without quoting

On 03/15/2012 12:23 PM, Tom Lane wrote: Lee Hachadoorian writes: COPY ... TO ... WITH CSV defaults to quoting string fields with embedded delimiters, quotes, and newlines. In pgAdmin I can execute to file and specify "no quoting" for the output, in which case I get (what I want) a fi

[SQL] COPY without quoting

string had to be at least one character. Any ideas? Best, --Lee -- Lee Hachadoorian PhD, Earth& Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make chan

Re: [SQL] optimize self-join query

so that they don't overlap anymore, there won't be any left that overlap two or three or more ranges. This can be accomplished with: WITH rank_epc AS ( SELECT record_id, epc_decimal_start AS range_low, epc_decimal_start + quantity AS range_high, rank() OVER (ORDER BY epc_decimal_start) FROM ft

Re: [SQL] distinguishing identical columns after joins

1, b_col1, a_col2, b_col2 ... for easy > comparison. And who can make sense of a 100 column results set anyway?:) > I don't know if someone has come up with a workaround, but to begin with note that the docs specify that when using the * "it is not possible to specify new names with

Re: [SQL] Aggregating by unique values

Perfect. Thanks, --Lee On 12/14/2010 03:23 AM, Filip Rembiałkowski wrote: try select zip, count(distinct id) from customer_service_date group by zip; 2010/12/14 Lee Hachadoorian <lee.hachadoor...@gmail.com>

[SQL] Aggregating by unique values

teger, address character varying, city character varying, state character varying, zip character varying, service_date date ) ; The table is missing a primary key field, but it would be (id, service_date) if it had one. Any suggestions to improve this? Thanks, --Lee -- Lee Hachadoorian PhD St

Re: [SQL] OT - load a shp file

se? I believe I have > postGIS install and now I want to load the US counties shp file. > > the following does not appear to work > shp2pgsql -s 4269 -I -W latin1 tl_2008_us_county.shp 2008_us_county | psql > > Plus I don't know what it does! > > Johnf > > --

Re: [SQL] Random sort with distinct

() Limit 10;   I don’t want to bring back the random number I just want the sort order to be random. How can I sort randomly?  This query breaks because random() is not in the select.   Thanks   Pam -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environme

Re: [SQL] How to get geometry enabled Tables form Postgresql/postgis

et me know.  I am waiting for your great response. Thanks and Regards, Venkat -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center

Re: [SQL] Question on COUNT performance

hether it makes more sense to do this via these function calls. --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center

Re: [SQL] Question on COUNT performance

but f_project_acl returned FALSE OR (2) there is no matching event, there IS a matching customer, but f_customer_acl returned FALSE And of course you don't know which plan_ids these might be true of. --Lee 2010/7/14 Lee Hachadoorian : > SELECT newfunc(uid); > > CREATE FU

Re: [SQL] Question on COUNT performance

ELECT statements to 1 SELECT statement. Obviously, not tested. Hope this is helpful. --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- 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] Round integer division

Thanks. That was a fairly recent post, too, but I couldn't come up with the right keyword search to find it. On 06/25/2010 07:25 PM, Adrian Klaver wrote: > On Friday 25 June 2010 3:53:01 pm Lee Hachadoorian wrote: > >> Is it documented anywhere that floating-po

Re: [SQL] Round integer division

r. Wouldn't that have to be intentionally programmed that way? --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://ww

[SQL] Round integer division

ing to use round(a::real/b::real) to generate a rounded result to dividing integers, and noticed sometimes 0.5 was truncated and sometimes it was rounded up. Couldn't find anything about this in the archives or the data type documentation. Is there something obvious that I'm I missing

Re: [SQL] Average of Array?

Thanks Tom & Pavel, these are very helpful. On Fri, Jun 25, 2010 at 12:29 PM, Pavel Stehule wrote: > Hello > > 2010/6/25 Lee Hachadoorian : >> Is there a function that returns the average of the elements of an >> array? I'm thinking of something that would wo

[SQL] Average of Array?

ot missing something. Thanks, -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- 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] Table Design for Hierarchical Data

Assuming I define a column naics as ltree, being able to query WHERE nlevel(naics) = [2|3|4] will work nicely, and with the right views, my users never have to see it. Thanks again to everyone who replied. Any further remarks, questions, comments are welcome. --Lee -- Lee Hachadoorian PhD Studen

[SQL] Table Design for Hierarchical Data

s be broken into separate columns? Should parent ids be stored in each node? More generally, what questions should I be asking to help decide what structure makes the most sense? Are there any websites, forums, or books that cover this kind of problem? Regards, --Lee -- Lee Hachadoorian PhD Stud

Re: [SQL] Crosstab Confusion

the final columns of the crosstab should just be blank. PS: The way I have constructed it, I would avoid using WHERE date >= ''2010-01-01''. If data from February gets into the table, it will aggregate data from, e.g. Jan 2 and Feb 2 as both being part of "Day 2". --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center

Re: [SQL] Crosstab Confusion

l the category headers from your source table like this: 'SELECT DISTINCT date_trunc(''day'', date) AS day WHERE date >= ''2010-01-01'' ORDER BY 1' Let me know if this gets you anywhere. --Lee On Mon, Feb 1, 2010 at 11:52 AM, Adam Sherm

Re: [SQL] Crosstab Confusion

hould be enough to trick the crosstab function into thinking it has something to fill the last columns. --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center

Re: [SQL] LIMIT BASED ON PERCENT

On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule wrote: > yes, and don't use 20%. > > select * from foo order by somecol limit (select (count(*)*0.2)::int from foo) > > Regards > Pavel Is this faster on a large table? Because (select (count(*)*20/100)) worked fine. -- Sent via pgsql-sql mailing l

Re: [SQL] LIMIT BASED ON PERCENT

. like: > select * from client limit 20% > I have tried (of course, with no success) this: > select * from client limit ((select count(*) from client)*20/100) > -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via

[SQL] Speed up UPDATE query?

ciated. Thanks, --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql