Re: [SQL] How to get the previous date?
Try this: select cast(mydate as date) -1 > Hi, > I'm using Enterprise DB PostgresPlus version 8.3. > Is there is a simple function or command like previous_date(mydate) which > when passed a particular date, simply returns the previous date? I tried > mydate - 1 > but this doesnt work. > Thanks, > Shruthi Почта со встроенным плеером находится здесь: http://mail.yandex.ru/promo/new/wysiwyg -- 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] 8.4.1 distinct query WITHOUT order by
> What are the ramifications of renaming the table (containing 8000 > rows) and creating a view of the same name? View does not admit ORDER BY clause, at least, Standard does not. Sergey -- 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] short-cutting if sum()>constant
Does PG support CTE? You can try it. > In response to Ivan Sergio Borgonovo : > > Развернуть > > Hi, > > > > I'd like to know if > > > > select sum(qty) from t where status=37; > > > > is > constant. > > > > qty is always >0. > > > > Is there a way to skip examining further rows and return a result > > ASAP? > I think no. > But you can create a new table with 2 columns: status (primary key) and > a column for the sum of qty. And you need a TRIGGER: update this table > for every insert, update and delete on your table t. > Now you can ask _this_ new table if the sum(qty) > your constant, this > should work very fast. > Just an idea... > Andreas > Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign -- 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] short-cutting if sum()>constant
> Sorry, but i don't know how a CTE can help in this case, can you explain I mean RECURSIVE CTE. You can check your condition on each iteration and stop execution when condition is false. Sergey > msi77 wrote: > > Развернуть > > Does PG support CTE? > Since 8.4 yes. > > Развернуть > > You can try it. > Sorry, but i don't know how a CTE can help in this case, can you explain > that? > Andreas > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] how to do this query
How about schols_selected like '%' || short_name || '%' > I have two tables: > students > stu_name > schols_selected > scholarships > schol_name > short_name > schols_selected is made up of scholarships the students have selected, > the field content will look like schol1:schol2:schol3 > I need a select that does something like this > select schol_name, short_name, stu_name, schols_selected > from scholarships, students > where short_name is in schols_selected > I have tried: > where short_name like '%' || schols_selected || '%' > but i'm not sure how to include a field result in between like operators. > how do I get a list of scholarships based one which student selected > it where the selected result is a string of colon seperated options? > thx, > -wj > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] how to do this query
> schols_seleced like '%' || short_name || '%' doesn't make sense to me select 'Ya-Ya' from (select 'schol1:schol2:schol3' as schols_selects ) AS X where schols_selects like '%schol2%' > If you look at the contents of these fields short name containts > "schol1", schols_selects containts "schol1:schol2:schol3" so > schols_seleced like '%' || short_name || '%' doesn't make sense to me > since the like operator is trying to find text1 in text two with %% > right? > 2010/1/9 msi77 : > > Развернуть > > How about > > > > schols_selected like '%' || short_name || '%' > > > Развернуть > > > I have two tables: > > > students > > > stu_name > > > schols_selected > > > scholarships > > > schol_name > > > short_name > > > schols_selected is made up of scholarships the students have selected, > > > the field content will look like schol1:schol2:schol3 > > > I need a select that does something like this > > > select schol_name, short_name, stu_name, schols_selected > > > from scholarships, students > > > where short_name is in schols_selected > > > I have tried: > > > where short_name like '%' || schols_selected || '%' > > > but i'm not sure how to include a field result in between like operators. > > > how do I get a list of scholarships based one which student selected > > > it where the selected result is a string of colon seperated options? > > > thx, > > > -wj > > > -- > > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-sql > > Здесь спама нет http://mail.yandex.ru/nospam/sign > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] Howto have a unique restraint on UPPER (textfield)
Hi, I think you need CS collation and UNIQUE(name). > Hi, > is there a way to define a unique restraint on UPPER (textfield)? > E.g. > mytable ( > name_id serial PRIMARY KEY, > name varchar(255), > UNIQUE ( upper (name) ) > ) > psql throws a syntax error because of the upper() function. > I need to prohibit that 2 of strings like cow, Cow, CoW appears in > the name-column. > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] combine SQL SELECT statements into one
Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') as count3 Serge > Good Evening, Good Morning Wherever you are whenever you may be reading this. > I am new to this email group and have some good experience with SQL and > PostgreSQL database. > I am currently working on a PHP / PostgreSQL project and I came upon > something I could not figure out in SQL. I was wondering if anyone here could > take a look and perhaps offer some guidance or assistance in helping me write > this SQL query. > Please Consider the following information: > --- > I have a postgresql table called 'inventory' that includes two fields: > 'model' which is a character varying field and 'modified' which is a > timestamp field. > So the table inventory looks something like this: > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > Now with the table, fields and data in mind look at the following three > queries: > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; > All three of the above queries work and provide results. However, I want to > combine the three into one SQL Statement that hits the database one time. How > can I do this in one SQL Statement? Is it possible with sub select? > Here is what result I am looking for from one SELECT statement using the data > example from above: > count1 | count2 | count3 > --- > 2 2 4 > Can this be done with ONE SQL STATEMENT? touching the database only ONE time? > Please let me know. > Thanx> :) > NEiL > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] combine SQL SELECT statements into one
Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') as count3 Serge > Good Evening, Good Morning Wherever you are whenever you may be reading this. > I am new to this email group and have some good experience with SQL and > PostgreSQL database. > I am currently working on a PHP / PostgreSQL project and I came upon > something I could not figure out in SQL. I was wondering if anyone here could > take a look and perhaps offer some guidance or assistance in helping me write > this SQL query. > Please Consider the following information: > --- > I have a postgresql table called 'inventory' that includes two fields: > 'model' which is a character varying field and 'modified' which is a > timestamp field. > So the table inventory looks something like this: > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > Now with the table, fields and data in mind look at the following three > queries: > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; > All three of the above queries work and provide results. However, I want to > combine the three into one SQL Statement that hits the database one time. How > can I do this in one SQL Statement? Is it possible with sub select? > Here is what result I am looking for from one SELECT statement using the data > example from above: > count1 | count2 | count3 > --- > 2 2 4 > Can this be done with ONE SQL STATEMENT? touching the database only ONE time? > Please let me know. > Thanx> :) > NEiL > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] please help me on regular expression
Why not to use select subjectid, height from tsakai.pheno where height like '%.%'; ? > Hi everybody, > I need a bit of help on postgres reqular expression. > With a table of the following definition: > Table "tsakai.pheno" > Column | Type | Modifiers > ---+---+--- > subjectid | integer | not null > height | character varying | not null > race | character varying | not null > blood | character varying | not null > I want to catch entries in height column that includes a > decimal point. Here's my attempt: > select subjectid, height > from tsakai.pheno > where height ~ '[:digit:]+.[:digit:]+'; > Which returns 0 rows, but if I get rid of where clause, > I get rows like: > subjectid | height > ---+ > 55379 | 70.5 > 55383 | 69 > 55395 | 70 > 56173 | 71 > 56177 | 65.5 > 56178 | 70 > . . > . . > And when I escape that dot after first plus sign with a backslash, > like this: > where height ~ '[:digit:]+\.[:digit:]+'; > then I get complaint: > WARNING: nonstandard use of escape in a string literal > LINE 3: where height ~ '[:digit:]+\.[:digit:]+'; > ^ > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. > From there, it was a downward spiral descent... > Please help. > Thank you. > Regards, > Tena Sakai > tsa...@gallo.ucsf.edu > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] "left join" not working?
> where p.id_line=1 this filters rows after join was applied. Try this select c.id_currency,max(p.modified_on) from currency c left join price_line p using (id_currency) where (p.id_line=1 or p.id_line is null) group by c.id_currency; > Hi, > This query: > select c.id_currency,max(p.modified_on) from currency c left join > price_line p using (id_currency) where p.id_line=1 group by > c.id_currency; > doesn't list all c.id_currency's, only those with a price_line. However > this one does: > select c.id_currency,max(p.modified_on) from currency c left join > price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by > c.id_currency; > How come? > Thanks, > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign -- 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] Howto get a group_number like row_number for groups
Hi, dense_rank() over (order by object) % 2 > Hi > I'd like to have an alternating colorindex in the output of a query that > consecutive rows that are the same within a colum the same number. > The query generates a readable output from a log-table and a few others > that hold referenced texts. > log (log_id int, log_event_fk int, object_fk int , ts timestamp) > events (event_id int, event text) > objects (object_id int, object text, is_active bool) > the easy part :) > mind the the ordering is not primarily based on the timestamp > select log_id, event, object, ts > from log > join events on event_id = log_event_fk > join objects on object_id = object_fk > where object.is_active > order by object, ts > Now I'd need a dynamically generated column that alternates between 0 > and 1 so that I can later color the rows where object is the same. > row_number() over (order by object, ts) % 2 > or rank() over (order by object, ts) % 2 > produces the 0/1 alternation for rows > When I create a subselect for objects that adds the colorindex and join > this to the log instead of objects, I get the group-color only if I > omit the sorting on the timestamp. > When I order the outer select by object, ts the colorindex gets 0 in > every row. :( > I'd like to get something as this > 3, up, dev3, 2010-4-2 10:00, 0 > 8, down, dev3, 2010-4-2 14:00, 0 > 9, down, dev3, 2010-4-2 15:00, 0 > 1, up, dev7, 2010-4-2 09:00, 1 > 5, down, dev7, 2010-4-2 17:00, 1 > 2, up, dev11, 2010-4-2 12:00, 0 > 7, down, dev11, 2010-4-2 13:00, 0 > . > . > regards :) > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign -- 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] inner join and limit
Hi, Some ways to do that: http://www.sql-ex.ru/help/select16.php > Hi list, > I have two table that are so represented: > t1: > id int primary key > ... other > t2: > id int primary key > t1id int fk(t1.id) > somedate date > ... other > data t1: > 1 | abcde > 2 | fghi > data t2: > 1 | 1 | 2010-05-23 > 2 | 1 | 2010-05-24 > 3 | 1 | 2010-05-25 > 4 | 2 | 2010-05-22 > 5 | 2 | 2010-05-26 > I'm trying to create a query where the data replied are: > join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date > order (of t2). > Data should be: > t1.id | t2.id | t2,somedate > 1 | 3 | 2010-05-25 > 2 | 5 | 2010-05-26 > As said, I'm trying, but without success... > Can be done for you? > Thanks, > Michele > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] Multiple subquery with insert into command ?
Use the following: INSERT INTO umzintersect (intersection,umz00_id,pop, comm_id, name_asci) SELECT intersection(umz_project.the_geom, lau2.the_geom), umz_project.umz00_id, umz_project.pop, lau2.comm_id, lau2.name_asci FROM umz_project, lau2,big_umz WHERE umz_project.umz00_id = big_umz.umz00_id AND umz_project.the_geom && lau2.the_geom AND umz_project.umz00_id in (23,58); http://www.sql-ex.ru/help/select11.php > Hello guys ! > I have problem with my query, i want to use the multiple insert possibility > of pgsql... > INSERT INTO umzintersect (intersection,umz00_id,pop, comm_id, name_asci) > (SELECT intersection(umz_project.the_geom, lau2.the_geom), > umz_project.umz00_id, umz_project.pop, lau2.comm_id, lau2.name_asci > FROM umz_project, lau2,big_umz > WHERE umz_project.umz00_id = big_umz.umz00_id AND umz_project.the_geom && > lau2.the_geom AND umz_project.umz00_id = 23), > (SELECT intersection(umz_project.the_geom, lau2.the_geom), > umz_project.umz00_id, umz_project.pop, lau2.comm_id, lau2.name_asci > FROM umz_project, lau2,big_umz > WHERE umz_project.umz00_id = big_umz.umz00_id AND umz_project.the_geom && > lau2.the_geom AND umz_project.umz00_id = 58); > I have this error code : > ** Erreur ** > ERREUR: erreur de syntaxe sur ou près de « , » > État SQL :42601 > Caractère : 353 > I don't understand where is my error ... > With only one select, the query run, but with two, i have this error.. > Perhaps insert into with multiple subquery is impossible ? So, how can i do > this query without loose the GREAT multi query possibility of postgresql ? > Thanks a lot in advance :) > Seb > Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign -- 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] Duplicate rows
A few ways to do this: http://www.sql-ex.ru/help/select17.php > Is there any way to remove a duplicate row from a table? Not my db but I have > to work with it. On version 7.4 right now. > Edward W. Rouse > Comsquared System, Inc. > 770-734-5301 > -- 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] is there a distinct function for comma lists ?
Hi, > And as bonus ... is there a way to find IDs that are in the list but not > in the table without creating a temporary table and use a join? Does below satisfy you? select * from (values (1), (2), (3), (5), (7), (11), (3), (6), (13), (13), (3), (11)) as X(a) where a not in(select id from mytable) Serge http://www.sql-ex.com/ > Hi, > For the problem 1 perhaps something like > select distinct unnest(ARRAY[ 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ]) > Regards, > Andreas > -Ursprüngliche Nachricht- > Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > Im Auftrag von Andreas > Gesendet: Dienstag, 7. September 2010 13:52 > An: pgsql-sql@postgresql.org > Betreff: [SQL] is there a distinct function for comma lists ? > Hi, > is there a distinct function for comma separated lists ? > I sometimes need to update tables where I got a set of IDs, like: > update mytable > set someattribute = 42 > where mytable.id in > ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... ) > So there are double entries in the list but in this case its just > overhead but no problem. > But for calculated values this would not allways be desirable. > update mytable > set someattribute = someattribute + 1 > where mytable.id in > ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... ) > How could I get a distinct list? Those lists can have 2000-3000 IDs > sometimes. > One solution was as follows but perhaps there is something more elegant? > update mytable > set someattribute = someattribute + 1 > where mytable.id in > ( select distinct id from mytable where id in ( 1, 2, 3, 5, 7, 11, 3, > 6, 13, 13, 3, 11 ... ) ) > And as bonus ... is there a way to find IDs that are in the list but not > in the table without creating a temporary table and use a join? > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] Getting top 2 by Category
There some ways to do this in one query. Look here: http://www.sql-ex.ru/help/select16.php --- 11.01.11, 22:00, "Ozer, Pam" :> This is probably very simple but I am drawing a blank. Do I need to create a cursor to iterate through a table to grab the top 2 magazines per category? Here is my table and some data . The results I need are at the bottom. Any help would be greatly appreciated: CREATE TABLE magazinecategory( magazinecategoryid smallint NOT NULL , magazineid smallint, categoryid smallint); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (1, 2, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (2, 8, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (3 9, 3); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (4, 10, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (5, 11, 4); INSERT INTO magazinecategory( magazinecategoryid, magazineid, categoryid) VALUES (6, 12,4); The results I want areCategoryID MagazineID3 23 84 104 11 Pam Ozer> -- 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] Queyring for columns which are exist in table.
Why would not use information schema to checking of existence of some column in a table: select column_name from information_schema.columns where table_name='tbl' -- 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] UNIQUE on everything except primary key
Do all the 10 columns make a natural key? 03.02.2011, 18:44, "gvim" : > I have a table with 11 columns and want to eliminate duplication on INSERTs. > What's the best method? The obvious solution would be to create a UNIQUE > index on everything except the primary key - 'id' - but that's a 10-column > index and doesn't sound very efficient. Am I missing something more obvious? > > gvim > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] Aggregates puzzle
Try this: select * from flood_ts F join (SELECT lid, MAX(value) AS mvalue, event_id FROM flood_ts GROUP BY lid, event_id) X on F.lid = X.lid and value = mvalue and X.event_id = F.event_id 01.04.2011, 00:15, "Mark Fenbers" : > SQL gurus, > > I have a table with 4 columns: lid(varchar), value(float), > obstime(datetime), event_id(integer) > > I want to find the MAX(value) and the time and date that it occurred > (obstime) in each group of rows where the lid and event_id are the > same. What I have works correctly in identifying the MAX(value) for the > given group, but I'm having trouble getting the corresponding obstime to > be reported along with it. > > Here's the SQL I have: > > SELECT lid, MAX(value), event_id > FROM flood_ts > GROUP BY lid, event_id > ORDER BY lid; > > If I add "obstime" to the SELECT list, then I need to add "value" to the > GROUP BY clause, which makes the MAX(value) function report *each row* > as a maximum. > > So, how can I revise my SQL to report the obstime that the MAX(value) > occurred? > > Any help is sincerely appreciated. > > Mark > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] Calculate the ratio
Hi, Try this select gridyear, gwcode, sum(case when cf=1 then area else o end) as cf1, sum(case when cf=0 then area else 0 end) as cf0 from table group by gridyear, gwcode http://sql-ex.ru/help/select8.php#case 15.08.2011, 16:21, "Andreas Forø Tollefsen" : > Hi all, > > This is probably quite simple. I want to calculate the ratio of each country > (gwcode) which experiences a 1 in one column (cf). > > Structure: > > gid; gridyear; gwcode; area; cf > > I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) > WHERE cf = 0 into another column and group by gridyear and gwcode. > Then i can see the ratio of the area of each country affected by cf. > > Any suggestions? > > Thanks. > > A. -- 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] F_TABLE_NUMBER_RANGE equivalent in Postgresql
Are you sure that dbo.F_TABLE_NUMBER_RANGE is in SQL Server? I suppose that this is UDF written by user. 16.08.2011, 08:53, "Yuan HOng" : > Hi, > > With MS-SQL sever, there is a built-in utility function > dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the > given range. For example: > > select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return: > > number > --- > 1 > 2 > 3 > 4 > 5 > > This is extremely convenient for generating consecutive dates. For > example, I want to find out the daily sales information and if on some > day there is no sales, the query should return 0 instead of a missing > date. > > In this case it is necessary to first create a list of consecutive > dates and then left join to some fact table. > > With the range function I can simply write something like > > select start_date + interval number > from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days) > > How does one accomplish such task with Postgresql? > > -- > Hong Yuan > > 大管家网上建材超市 > 装修装潢建材一站式购物 > http://www.homemaster.cn > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] Re: how can I get the length of columns of a table by system tables/views
Why not to use information schema: select column_name, character_maximum_length from information_schema.columns where table_name ='table name' 09.09.2011, 16:53, "shuaixf" : > SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) > FROM pg_catalog.pg_attribute a > WHERE a.attrelid = (SELECT pg_class.oid > FROM pg_class INNER JOIN pg_namespace > ON (pg_class.relnamespace = pg_namespace.oid > AND lower(pg_namespace.nspname) = 'public') > WHERE pg_class.relname='tablename') > AND a.attnum > 0 AND NOT a.attisdropped > ORDER BY a.attnum; > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-can-I-get-the-length-of-columns-of-a-table-by-system-tables-views-tp4780987p4786248.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] Duplicate information in parent and child tables
Maybe this will help http://sql-ex.ru/help/select18.php 04.04.2012, 02:03, "JORGE MALDONADO" : > I have a parent table and a child table. In order to detect duplicate > information I need to consider both tables. This means that a "duplicate > record" consists of one parent record and one or more child records. Is > there any standard approach to solve this issue? > > Respectfully, > Jorge Maldonado -- 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] SELECT 1st field
Try this create view v(id) as select * from func(5); if your function returns one column. 15.05.2012, 10:01, "Jan Bakuwel" : > Hi, > > I've spend some time checking the documentation but haven't been able to > find what I'm looking for. > I've got a function that returns a set of integers and a view that > selects from the function. > What I need is the ability to name the column in the view, ie. > > create function func(i int) returns setof integer as $$ > ... > ...code > ... > $$ language plpythonu volatile; > > create view v as select 1 as "id" from func(5); > > In other words I'd like to refer to the first (and only) field returned > and give that an alias, in this case "id". > > In some SQL dialects you can use "select 1" to select the first field, > "select 2" to select the 2nd field and so on. > > Any suggestions? > > regards, > Jan -- 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] Lowest 2 items per
A few of approaches to solve this problem: http://sql-ex.com/help/select16.php 01.06.2012, 18:34, "Relyea, Mike" : > I need a little help putting together a query. I have the tables listed > below and I need to return the lowest two consumables (ranked by cost > divided by yield) per printer, per color of consumable, per type of > consumable. > > CREATE TABLE printers > ( > printerid serial NOT NULL, > make text NOT NULL, > model text NOT NULL, > CONSTRAINT printers_pkey PRIMARY KEY (make , model ), > CONSTRAINT printers_printerid_key UNIQUE (printerid ), > ) > > CREATE TABLE consumables > ( > consumableid serial NOT NULL, > brand text NOT NULL, > partnumber text NOT NULL, > color text NOT NULL, > type text NOT NULL, > yield integer, > cost double precision, > CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ), > CONSTRAINT consumables_consumableid_key UNIQUE (consumableid ) > ) > > CREATE TABLE printersandconsumables > ( > printerid integer NOT NULL, > consumableid integer NOT NULL, > CONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid , > consumableid ), > CONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY > (consumableid) > REFERENCES consumables (consumableid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE, > CONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY > (printerid) > REFERENCES printers (printerid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE > ) > > I've pulled together this query which gives me the lowest consumable per > printer per color per type, but I need the lowest two not just the first > lowest. > > SELECT printers.make, printers.model, consumables.color, > consumables.type, min(cost/yield) AS cpp > FROM printers > JOIN printersandconsumables ON printers.printerid = > printersandconsumables.printerid > JOIN consumables ON consumables.consumableid = > printersandconsumables.consumableid > WHERE consumables.cost Is Not Null > AND consumables.yield Is Not Null > GROUP BY printers.make, printers.model, consumables.color, > consumables.type > ORDER BY make, model; > > After doing a google search I didn't come up with anything that I was > able to use so I'm asking you fine folks! > > Mike > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] Lowest 2 items per
Thank you for reply, Oliver. I want that you'll pay attention to the learn exercises which can by made under PostgreSQL among few other DBMS: http://sql-ex.ru/exercises/index.php?act=learn 02.06.2012, 19:00, "Oliveiros" : > Nice resource, msi77. > > Thanx for sharing. > > I wasn't aware of none of these techniques, actually, so I tried to start > from scratch, but I should've realized that many people in the past had the > same problem as Mike and I should have googled a little instead of trying to > re-invent the wheel. > > Anyway, this is great information and I'm sure it will be useful in the > future. > Again thanx for sharing. > > Best, > Oliver > > 2012/6/2 msi77 >> A few of approaches to solve this problem: >> >> http://sql-ex.com/help/select16.php >> >> 01.06.2012, 18:34, "Relyea, Mike" : >>> I need a little help putting together a query. I have the tables listed >>> below and I need to return the lowest two consumables (ranked by cost >>> divided by yield) per printer, per color of consumable, per type of >>> consumable. >>> >>> CREATE TABLE printers >>> ( >>> printerid serial NOT NULL, >>> make text NOT NULL, >>> model text NOT NULL, >>> CONSTRAINT printers_pkey PRIMARY KEY (make , model ), >>> CONSTRAINT printers_printerid_key UNIQUE (printerid ), >>> ) >>> >>> CREATE TABLE consumables >>> ( >>> consumableid serial NOT NULL, >>> brand text NOT NULL, >>> partnumber text NOT NULL, >>> color text NOT NULL, >>> type text NOT NULL, >>> yield integer, >>> cost double precision, >>> CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ), >>> CONSTRAINT consumables_consumableid_key UNIQUE (consumableid ) >>> ) >>> >>> CREATE TABLE printersandconsumables >>> ( >>> printerid integer NOT NULL, >>> consumableid integer NOT NULL, >>> CONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid , >>> consumableid ), >>> CONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY >>> (consumableid) >>> REFERENCES consumables (consumableid) MATCH SIMPLE >>> ON UPDATE CASCADE ON DELETE CASCADE, >>> CONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY >>> (printerid) >>> REFERENCES printers (printerid) MATCH SIMPLE >>> ON UPDATE CASCADE ON DELETE CASCADE >>> ) >>> >>> I've pulled together this query which gives me the lowest consumable per >>> printer per color per type, but I need the lowest two not just the first >>> lowest. >>> >>> SELECT printers.make, printers.model, consumables.color, >>> consumables.type, min(cost/yield) AS cpp >>> FROM printers >>> JOIN printersandconsumables ON printers.printerid = >>> printersandconsumables.printerid >>> JOIN consumables ON consumables.consumableid = >>> printersandconsumables.consumableid >>> WHERE consumables.cost Is Not Null >>> AND consumables.yield Is Not Null >>> GROUP BY printers.make, printers.model, consumables.color, >>> consumables.type >>> ORDER BY make, model; >>> >>> After doing a google search I didn't come up with anything that I was >>> able to use so I'm asking you fine folks! >>> >>> Mike >>> >>> -- >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- 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] strange corruption?
Hi, Maybe you have triggers on the target table. 27.12.2012, 18:28, "John Fabiani" : > Hi, > I have the following statement in a function. > > UPDATE orderseq > SET orderseq_number = (orderseq_number + 1) > WHERE (orderseq_name='InvcNumber'); > > All it does is update a single record by incrementing a value (int). > > But it never completes. This has to be some sort of bug. Anyone have a > thought what would cause this to occur. To my knowledge it was working > and does work in other databases. > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] Query execution based on a condition
Change the conditions of other 3 queries, so those shall give empty row sets depending on your value. 29.12.2012, 23:06, "JORGE MALDONADO" : > I have a query similar to the one shown below but, depending on the value of > a field, only the first SELECT statement shoud execute and the other 3 should > be ignored. Is there a way to achieve this situation? > > SELECT fields FROM tblTable WHERE condition > UNION > > SELECT fields FROM tblTable WHERE condition > > UNION > > SELECT fields FROM tblTable WHERE condition > UNION > > SELECT fields FROM tblTable WHERE condition > > Respectfully, > Jorge Maldonado -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
