Thread: a wierd query

a wierd query

From
"ashok@kalculate.com"
Date:
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/ .




Re: a wierd query

From
sad
Date:
> 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;



Re: a wierd query

From
Silke Trißl
Date:

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

>  
>




Re: a wierd query

From
Achilleus Mantzios
Date:
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



Re: a wierd query

From
Peter Childs
Date:

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


Re: a wierd query

From
Yasir Malik
Date:
-----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-----


Re: a wierd query

From
Edmund Bacon
Date:
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>


Re: a wierd query

From
sad
Date:
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



Re: a wierd query

From
Stephan Szabo
Date:
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?



Re: a wierd query

From
"Stijn Vanroye"
Date:
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
>


Re: a wierd query

From
"Stijn Vanroye"
Date:
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
> >
>


Re: a wierd query

From
sad
Date:
> 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.



Re: a wierd query

From
"Stijn Vanroye"
Date:
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)
>


Re: a wierd query

From
Stephan Szabo
Date:
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.


Re: a wierd query

From
"Stijn Vanroye"
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.
>


Re: a wierd query

From
Christoph Haller
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