Re: [SQL] How can I selet rows which have 2 columns values cross equal?
On Mar 11, 2006, at 16:46 , Michael Glaesemann wrote: select t1.id as t1_id, t2.id as t2_id from test t1 join test t2 on (t1.a = t2.b and t1.b = t2.a) where t1.a < t2.a; t1_id | t2_id ---+--- 4 | 7 1 | 2 (2 rows) Just a follow-up (mostly to myself): I've been toying with using natural joins recently, and here's the same query rewritten to use a natural join: select id as t1_id, t2_id from test t1 natural join ( select id as t2_id , a as b , b as a from test ) t2 where id < t2_id; t1_id | t2_id ---+--- 4 | 7 1 | 2 (2 rows) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] input from a external text file......!
Hi All.! I just want to know one thing that is it possible with PGSQL that, if I want to insert and execute a query from a external text file instead of giving it at the pgsql prompt? just like in Oracle the file having query is executed with a '@ filename' statement at the sql prompt..! plz help me and mail me @ [EMAIL PROTECTED], it's urgent. thanks in advance...! (i have searched alot, but didn't found anything)-- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064) (+911744293789) "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [SQL] input from a external text file......!
AKHILESH GUPTA <[EMAIL PROTECTED]> schrieb: > Hi All.! > I just want to know one thing that is it possible with PGSQL that, > if I want to insert and execute a query from a external text file instead of > giving it at the pgsql prompt? in psql, try simple "\i your_file.sql" to execute the commands within this file. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] input from a external text file......!
inside psql, type : \i filename On Sat, 11 Mar 2006 11:29:20 +0100, AKHILESH GUPTA <[EMAIL PROTECTED]> wrote: Hi All.! I just want to know one thing that is it possible with PGSQL that, if I want to insert and execute a query from a external text file instead of giving it at the pgsql prompt? just like in Oracle the file having query is executed with a '@ filename' statement at the sql prompt..! plz help me and mail me @ [EMAIL PROTECTED], it's urgent. thanks in advance...! (i have searched alot, but didn't found anything) -- Thanks & Regards, Akhilesh DAV Institute of Management Faridabad(Haryana) GSM:-(+919891606064) (+911744293789) "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Merging rows into one result?
Is it possible to use SQL to merge data into one result? A theorethical example to explain: tbl_test ( id integer, information varchar(25)) id | information ---+-- 1 | Yo 2 | Go away 1 | Stay put 3 | Greetings Please note id is not unique and not a primary key. and I wonder if there is any functions to "merge" data (sort of concat'ing). A normal: select information from tbl_test where id=1 would result in the rows Yo Stay put I would like a single row result in the format of: Yo Stay put Any ideas on this? Best regards Jesper K. Pedersen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Merging rows into one result?
Jesper K. Pedersen <[EMAIL PROTECTED]> schrieb: > Is it possible to use SQL to merge data into one result? > > A theorethical example to explain: > > tbl_test ( > id integer, > information varchar(25)) > > id | information > ---+-- > 1 | Yo > 2 | Go away > 1 | Stay put > 3 | Greetings > > Please note id is not unique and not a primary key. > > and I wonder if there is any functions to "merge" data (sort of > concat'ing). > A normal: select information from tbl_test where id=1 > would result in the rows > Yo > Stay put > > I would like a single row result in the format of: > Yo Stay put Yes, of corse, this is possible. You need a own aggregate-function. A similar example for this task can you find here: http://www.zigo.dhs.org/postgresql/#comma_aggregate I think, it is very simple to rewrite this example for your purpose. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Merging rows into one result?
Hi, On Mar 11 05:31, Jesper K. Pedersen wrote: > Is it possible to use SQL to merge data into one result? test=# SELECT id, info FROM concat_t; id | info +-- 1 | A 2 | B 1 | AA 3 | C 1 | D 1 | DD (6 rows) test=# SELECT array_to_string(ARRAY(SELECT info FROM concat_t WHERE id = 1), ' '); array_to_string - A AA D DD (1 row) HTH Regards. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Merging rows into one result?
On Sat, 11 Mar 2006 17:43:37 +0100 Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > Jesper K. Pedersen <[EMAIL PROTECTED]> schrieb: > > > Is it possible to use SQL to merge data into one result? > > > > A theorethical example to explain: > > > > tbl_test ( > > id integer, > > information varchar(25)) > > > > id | information > > ---+-- > > 1 | Yo > > 2 | Go away > > 1 | Stay put > > 3 | Greetings > > > > Please note id is not unique and not a primary key. > > > > and I wonder if there is any functions to "merge" data (sort of > > concat'ing). > > A normal: select information from tbl_test where id=1 > > would result in the rows > > Yo > > Stay put > > > > I would like a single row result in the format of: > > Yo Stay put > > Yes, of corse, this is possible. You need a own aggregate-function. A > similar example for this task can you find here: > > http://www.zigo.dhs.org/postgresql/#comma_aggregate > > I think, it is very simple to rewrite this example for your purpose. > The comma aggregate worked like a charm. Thank's ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Merging rows into one result?
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Jesper K.
Pedersen") would write:
> Is it possible to use SQL to merge data into one result?
>
> A theorethical example to explain:
>
> tbl_test (
> id integer,
> information varchar(25))
>
> id | information
> ---+--
> 1 | Yo
> 2 | Go away
> 1 | Stay put
> 3 | Greetings
>
> Please note id is not unique and not a primary key.
>
> and I wonder if there is any functions to "merge" data (sort of
> concat'ing).
> A normal: select information from tbl_test where id=1
> would result in the rows
> Yo
> Stay put
>
> I would like a single row result in the format of:
> Yo Stay put
>
> Any ideas on this?
Sure, you could create a custom aggregate to append them using spaces.
Look in the PostgreSQL documentation under "CREATE AGGREGATE." If you
check the online version at PostgreSQL.org, there are comments showing
examples...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/wp.html
--Despite Pending :Alarm--
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] pgsql aggregate: conditional max
Hi, I need a special aggregation function. For instance, given the following table data: aid| cat | weight --+-+- a1 | Drama | 1 a1 | Romance | 6 a1 | Short | 1 a1 | Other | 7 a2 | Comedy | 1 a2 | Drama | 2 a3 | Drama | 1 a3 | Adult | 2 a3 | Comedy | 1 a3 | Other | 1 I want to group by "aid" and choose the category (i.e., "cat") with the largest "weight": aid | max_weighted_cat +- a1 | Other a2 | Drama a3 | Adult Any ideas? Thank you! :) -- All best, Weimao Weimao Ke Indiana University Bloomington School of Library and Information Science http://ella.slis.indiana.edu/~wke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pgsql aggregate: conditional max
Weimao Ke wrote: Hi, I need a special aggregation function. For instance, given the following table data: aid| cat | weight --+-+- a1 | Drama | 1 a1 | Romance | 6 a1 | Short | 1 a1 | Other | 7 a2 | Comedy | 1 a2 | Drama | 2 a3 | Drama | 1 a3 | Adult | 2 a3 | Comedy | 1 a3 | Other | 1 I want to group by "aid" and choose the category (i.e., "cat") with the largest "weight": aid | max_weighted_cat +- a1 | Other a2 | Drama a3 | Adult Any ideas? Thank you! :) Should be able to do this with a standard max() aggregate. select aid, cat, max(weight) from table group by aid, cat; Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pgsql aggregate: conditional max
On Sun, Mar 12, 2006 at 12:09:48AM -0500, Weimao Ke wrote: > I want to group by "aid" and choose the category (i.e., "cat") with the > largest "weight": > > aid | max_weighted_cat > +- > a1 | Other > a2 | Drama > a3 | Adult PostgreSQL has a non-standard DISTINCT ON clause that would work. See the weather_reports example in the documentation for SELECT: http://www.postgresql.org/docs/8.1/interactive/sql-select.html Try this query against your example data: SELECT DISTINCT ON (aid) aid, cat FROM tablename ORDER BY aid, weight DESC, cat; If multiple rows for a given aid match that aid's max weight then the above query will return the first matching row according to the given sort order. Some people object to DISTINCT ON because it's non-deterministic if you don't order by enough columns. Here's something more standard; it'll return all rows that match a given aid's max weight: SELECT aid, cat FROM tablename AS t JOIN (SELECT aid, max(weight) AS weight FROM tablename GROUP BY aid) AS s USING (aid, weight); -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pgsql aggregate: conditional max
On Sun, Mar 12, 2006 at 12:34:57AM -0500, Jeffrey Melloy wrote: > Should be able to do this with a standard max() aggregate. > > select aid, cat, max(weight) > from table > group by aid, cat; That query returns the maximum weight for each (aid, cat) pair. Against the example data it returns the entire table, not the (aid, cat) pair with the max weight for a given aid. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
