Обсуждение: a wierd query
hi
i have a wierd problem and i require an equally weird query.
1) backgound Table test: CREATE TABLE main_table ( string_A varchar( 20), string_B varchar(
20), ); -- both columns are identical in nature and usage INSERT INTO main_table
VALUES('abcd','qrst'); INSERT INTO main_table VALUES('efgh','efgh'); INSERT INTO main_table VALUES('ijkl','abcd');
INSERT INTO main_table VALUES('abcd','ijkl'); INSERT INTO main_table VALUES('qrst','uvwx');
2) problem: i require a query that gives me a result set of the form
'abcd' 'efgh' 'ijkl' 'qrst' 'uvwx'
that is i require the dictinct values from (visualizing each column
result as a set) the union of the two columns
3) questions
a) is a query like this possible that can give me the desired result b) if so what would it be.
4) remarks i can get the solution using a temporary table and with repeated "insert into temporary select
$columnfrom main_table"
thanks in advance
ashok
--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .
> i require the dictinct values from (visualizing each column > result as a set) the union of the two columns select distinct a as F from table union select distinct b as F from table;
ashok@kalculate.com schrieb:
>hi
>
>i have a wierd problem and i require an equally weird query.
>1) backgound
> Table test:
> CREATE TABLE main_table (
> string_A varchar( 20),
> string_B varchar( 20),
> );
> -- both columns are identical in nature and usage
> INSERT INTO main_table VALUES('abcd','qrst');
> INSERT INTO main_table VALUES('efgh','efgh');
> INSERT INTO main_table VALUES('ijkl','abcd');
> INSERT INTO main_table VALUES('abcd','ijkl');
> INSERT INTO main_table VALUES('qrst','uvwx');
>
>2) problem:
>
> i require a query that gives me a result set of the form
>
> 'abcd'
> 'efgh'
> 'ijkl'
> 'qrst'
> 'uvwx'
>
> that is i require the dictinct values from (visualizing each column
>result as a set) the union of the two columns
>
>3) questions
>
> a) is a query like this possible that can give me the desired result
> b) if so what would it be.
>
Yes, the follwoingselect t1.string_a from main_table t1
union
select t2.string_b from main_table t2;
>
>4) remarks
>
> i can get the solution using a temporary table and with repeated
> "insert into temporary select $column from main_table"
>
>
Why, SQL does the trick!
>
>thanks in advance
>
>ashok
>
>
>
Silke
>
>
SELECT foo.value from (select string_a as value from main_table UNION
select string_b as value from main_table) as foo;
O kyrios ashok@kalculate.com egrapse stis May 13, 2004 :
> hi
>
> i have a wierd problem and i require an equally weird query.
> 1) backgound
> Table test:
> CREATE TABLE main_table (
> string_A varchar( 20),
> string_B varchar( 20),
> );
> -- both columns are identical in nature and usage
> INSERT INTO main_table VALUES('abcd','qrst');
> INSERT INTO main_table VALUES('efgh','efgh');
> INSERT INTO main_table VALUES('ijkl','abcd');
> INSERT INTO main_table VALUES('abcd','ijkl');
> INSERT INTO main_table VALUES('qrst','uvwx');
>
> 2) problem:
>
> i require a query that gives me a result set of the form
>
> 'abcd'
> 'efgh'
> 'ijkl'
> 'qrst'
> 'uvwx'
>
> that is i require the dictinct values from (visualizing each column
> result as a set) the union of the two columns
>
> 3) questions
>
> a) is a query like this possible that can give me the desired result
> b) if so what would it be.
>
> 4) remarks
>
> i can get the solution using a temporary table and with repeated
> "insert into temporary select $column from main_table"
>
>
> thanks in advance
>
> ashok
>
> --------------------------------------------------------------------
> mail2web - Check your email from the web at
> http://mail2web.com/ .
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
-Achilleus
On Thu, 13 May 2004, ashok@kalculate.com wrote:
> hi
>
> i have a wierd problem and i require an equally weird query.
> 1) backgound
> Table test:
> CREATE TABLE main_table (
> string_A varchar( 20),
> string_B varchar( 20),
> );
> -- both columns are identical in nature and usage
> INSERT INTO main_table VALUES('abcd','qrst');
> INSERT INTO main_table VALUES('efgh','efgh');
> INSERT INTO main_table VALUES('ijkl','abcd');
> INSERT INTO main_table VALUES('abcd','ijkl');
> INSERT INTO main_table VALUES('qrst','uvwx');
>
> 2) problem:
>
> i require a query that gives me a result set of the form
>
> 'abcd'
> 'efgh'
> 'ijkl'
> 'qrst'
> 'uvwx'
>
> that is i require the dictinct values from (visualizing each column
> result as a set) the union of the two columns
>
> 3) questions
>
> a) is a query like this possible that can give me the desired result
> b) if so what would it be.
>
> 4) remarks
>
> i can get the solution using a temporary table and with repeated
> "insert into temporary select $column from main_table"
>
>
select distinct t from (select string_A as t from main_table union
select string_B as t from main_table);
or
select f from (select A as t from main_table union select B as t from
main_table) group by t order by t;
hope that helps
Peter Childs
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Yes you can. Try this:
(select string_A from main_table) union (select string_B from main_table)
Yasir
On Thu, 13 May 2004, ashok@kalculate.com wrote:
> Date: Thu, 13 May 2004 04:07:08 -0400
> From: "ashok@kalculate.com" <ashok@kalculate.com>
> To: pgsql-sql@postgresql.org
> Subject: [SQL] a wierd query
>
> hi
>
> i have a wierd problem and i require an equally weird query.
> 1) backgound
> Table test:
> CREATE TABLE main_table (
> string_A varchar( 20),
> string_B varchar( 20),
> );
> -- both columns are identical in nature and usage
> INSERT INTO main_table VALUES('abcd','qrst');
> INSERT INTO main_table VALUES('efgh','efgh');
> INSERT INTO main_table VALUES('ijkl','abcd');
> INSERT INTO main_table VALUES('abcd','ijkl');
> INSERT INTO main_table VALUES('qrst','uvwx');
>
> 2) problem:
>
> i require a query that gives me a result set of the form
>
> 'abcd'
> 'efgh'
> 'ijkl'
> 'qrst'
> 'uvwx'
>
> that is i require the dictinct values from (visualizing each column
> result as a set) the union of the two columns
>
> 3) questions
>
> a) is a query like this possible that can give me the desired result
> b) if so what would it be.
>
> 4) remarks
>
> i can get the solution using a temporary table and with repeated
> "insert into temporary select $column from main_table"
>
>
> thanks in advance
>
> ashok
>
> --------------------------------------------------------------------
> mail2web - Check your email from the web at
> http://mail2web.com/ .
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (NetBSD)
iQEVAwUBQKN2/+J7vYSSIbWdAQKklQf+JPhyMpbhEVX/4t70r1m6RFPXkm2VgbOz
Dyxkjhbko07c+YcnVbHmk/8D0d+1L0Qx23vytCfvqRS29O5tzwDFrSfHCZQ8WE4C
H7P0377jfa/LxgAeaUNnDfhhGj+qUI649i2QDSzdalVVwKtUl/aKdw0+evveuUXZ
QBYvVeoFU9KrnqBbQNW6AQOM8vfnYG3cxcb87krRy/b2EgZE462o2O3jGhqvlmrU
8eKJCrEnv4t53IOI3J2WECKbuSomTrUAqfUWbpL6g7zrOpkuCTqzTuOrx+7ISMTR
zyY36zUDeOB/A7u3PEh+wQz/Yqdq1Gu9GQ3kIsgao1WA+K3tj1ceKA==
=zMMM
-----END PGP SIGNATURE-----
sad wrote: > select distinct a as F from table > union > select distinct b as F from table; > Note that UNION only returns the unique values of the union You can get repeated values by using UNION ALL. -- Edmund Bacon <ebacon@onesystem.com>
On Thursday 13 May 2004 19:27, you wrote: > sad wrote: > > select distinct a as F from table > > union > > select distinct b as F from table; > > Note that UNION only returns the unique values of the union > You can get repeated values by using UNION ALL. read the original problem look at the DISTINCT clause in my query and think again
On Fri, 14 May 2004, sad wrote: > On Thursday 13 May 2004 19:27, you wrote: > > sad wrote: > > > select distinct a as F from table > > > union > > > select distinct b as F from table; > > > > Note that UNION only returns the unique values of the union > > You can get repeated values by using UNION ALL. > > read the original problem > look at the DISTINCT clause in my query > and think again What about the fact that union already removes duplicates?
Are you sure about that Edmund? I have the following query: select distinct on (task_id, date) task_id, workhour_id, date from ( select task_id,workhour_id, begindate as date from workhour UNION select task_id, workhour_id, enddate as date from workhour )as dist1 which returns me 2763 rows in my case if I use the query without the top level select, like this: select task_id, workhour_id, begindate as date from workhour UNION select task_id, workhour_id, enddate as date from workhour I get 7146 rows. If I understand correctly there would be no need for the top level select if UNION would be to only return unique values.But given my test results this doesn't seem to be the case. Am I missing something or am I misinterpreting something?I mean I'm sure you get this information out of the documentation, that's why this question has risen. Kind regards, Stijn Vanroye > -----Original Message----- > From: Edmund Bacon [mailto:ebacon@onesystem.com] > Sent: donderdag 13 mei 2004 17:28 > To: sad > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] a wierd query > > > sad wrote: > > select distinct a as F from table > > union > > select distinct b as F from table; > > > > Note that UNION only returns the unique values of the union > You can get repeated values by using UNION ALL. > > > -- > Edmund Bacon <ebacon@onesystem.com> > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Sorry, I forgot one situation: if I run the query like so: select distinct on (task_id, begindate) task_id, workhour_id, begindate as date from workhour UNION select distinct on (task_id, enddate) task_id, workhour_id, enddate as date from workhour I get yet another value: 2961 rows. So I got 3 different result sets for 3 different ways to run the query. Even in this last case the UNION doesn't seem toonly return unique values, and I will still need the top-level select. > -----Original Message----- > From: Stijn Vanroye > Sent: maandag 17 mei 2004 9:26 > To: pgsql-sql@postgresql.org > Cc: 'Edmund Bacon' > Subject: RE: [SQL] a wierd query > > > Are you sure about that Edmund? > > I have the following query: > select distinct on (task_id, date) task_id, workhour_id, date from > ( > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > )as dist1 > which returns me 2763 rows in my case > > if I use the query without the top level select, like this: > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > I get 7146 rows. > > If I understand correctly there would be no need for the top > level select if UNION would be to only return unique values. > But given my test results this doesn't seem to be the case. > Am I missing something or am I misinterpreting something? I > mean I'm sure you get this information out of the > documentation, that's why this question has risen. > > > Kind regards, > > Stijn Vanroye > > > -----Original Message----- > > From: Edmund Bacon [mailto:ebacon@onesystem.com] > > Sent: donderdag 13 mei 2004 17:28 > > To: sad > > Cc: pgsql-sql@postgresql.org > > Subject: Re: [SQL] a wierd query > > > > > > sad wrote: > > > select distinct a as F from table > > > union > > > select distinct b as F from table; > > > > > > > Note that UNION only returns the unique values of the union > > You can get repeated values by using UNION ALL. > > > > > > -- > > Edmund Bacon <ebacon@onesystem.com> > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > >
> I forgot one situation: > > if I run the query like so: > select distinct on (task_id, begindate) task_id, workhour_id, > begindate as date from workhour UNION > select distinct on (task_id, enddate) task_id, workhour_id, enddate > as date from workhour I get yet another value: 2961 rows. > > So I got 3 different result sets for 3 different ways to run the query. > Even in this last case the UNION doesn't seem to only return unique values, > and I will still need the top-level select. if we suppose this situation possible to program in SQL it causes data-loss in query (i mean unpredictable query result: if you have two records (f=1,b=2),(f=1,b=3) "distinct ON f" makes a value of b meaningless) So you MUST NOT select that way P.S. This situation means: you have wrong data structure.
First of all, I don't select distinct on 1 value, but on 2. Meaning I want each unique combination of task_id (or employee_idin this example) and date. That way both fields still have meaning. the workhour_id field is indeed redundant, but was still there from some pevious testing work. (It is now removed). bothother fields are used. What I'm trying to achieve here is the following: for each task get all date's in wich that task has been performed (andeach date only once per task). Since workhours have a begin date and time, as well as an end date and time. Begin- andenddate don't have to be the same day. But if a task is performed starting monday and lasting till tueseday, both dayshave to be included in the count. What I use now is this: select distinct on (date, employee_id) employee_id, date from ( select distinct on (begindate,employee_id) begindate as date, employee_id from workhour UNION select distinct on (enddate, employee_id)enddate as date, employee_id from workhour )as dist The workhour table looks something like this: workhour(workhour_id, task_id, employee_id, begindate, begintime, enddate, endtime) I Can't think of any other solution to achieve this. As far as I can tell, Im not missing something and I don't have meaninglessfields (suggestions always welcome). Later on some grouping will be done to count the number of days worked ona certain task (or by a certain employee) in a given period. This still keeps my question open wether or not a UNION does only show unique values in the union. > -----Original Message----- > From: sad [mailto:sad@bankir.ru] > Sent: maandag 17 mei 2004 9:13 > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] a wierd query > > > > I forgot one situation: > > > > if I run the query like so: > > select distinct on (task_id, begindate) task_id, workhour_id, > > begindate as date from workhour UNION > > select distinct on (task_id, enddate) task_id, > workhour_id, enddate > > as date from workhour I get yet another value: 2961 rows. > > > > So I got 3 different result sets for 3 different ways to > run the query. > > Even in this last case the UNION doesn't seem to only > return unique values, > > and I will still need the top-level select. > > if we suppose this situation possible to program in SQL > it causes data-loss in query > (i mean unpredictable query result: > if you have two records (f=1,b=2),(f=1,b=3) > "distinct ON f" makes a value of b meaningless) > > So you MUST NOT select that way > > P.S. This situation means: you have wrong data structure. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
On Mon, 17 May 2004, Stijn Vanroye wrote: > Are you sure about that Edmund? > > I have the following query: > select distinct on (task_id, date) task_id, workhour_id, date from > ( > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > )as dist1 This gets you first rows distincted by task_id, workhour_id and date and then only rows distincted by task_id and date (and an unpredictable workhour_id). > if I use the query without the top level select, like this: > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour This gets rows distincted by task_id, workhour_id and date.
I understand, thanks. First: as I said in my previous post, the workhour_id was left behind by mistake and has since been removed. So it seems that I can ommit the distinct completely and just use the query in it's simpelest form, like this: select employee_id,begindate as date from workhour UNION select employee_id, enddate as date from workhour And I would get a list of all dates (as well begin- as enddates) where a date can occure only once with each employee? Altough I didn't start this thread I'm learing some usefull things here, so some thanks to the people who replied (and startedthe thread) are in place here. Kind regards, Stijn Vanroye > -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] > Sent: maandag 17 mei 2004 17:01 > To: Stijn Vanroye > Cc: pgsql-sql@postgresql.org; Edmund Bacon > Subject: Re: [SQL] a wierd query > > On Mon, 17 May 2004, Stijn Vanroye wrote: > > > Are you sure about that Edmund? > > > > I have the following query: > > select distinct on (task_id, date) task_id, > workhour_id, date from > > ( > > select task_id, workhour_id, begindate as date from workhour > > UNION > > select task_id, workhour_id, enddate as date from workhour > > )as dist1 > > This gets you first rows distincted by task_id, workhour_id and date > and then only rows distincted by task_id and date (and an > unpredictable > workhour_id). > > > if I use the query without the top level select, like this: > > select task_id, workhour_id, begindate as date from workhour > > UNION > > select task_id, workhour_id, enddate as date from workhour > > This gets rows distincted by task_id, workhour_id and date. >
> > select distinct on (date, employee_id) employee_id, date from > ( > select distinct on (begindate, employee_id) begindate as date, employ= > ee_id from workhour > UNION > select distinct on (enddate, employee_id) enddate as date, employee_i= > d from workhour > )as dist > > Just as a side note If you don't use ORDER BY when using DISTINCT ON you'll have unpredictable results. Regards, Christoph