OT: seeking query help, where? 
Author Message
 OT: seeking query help, where?

First, sorry for the OT, flame me off-list!

I'm a sysadmin being impressed into dba service. i've been getting along
pretty well writing queries and making reports, but i've got some questions.
suggestions for a good list/forum for help?

from two tables both with email_addr columns, i want a distinct list of all
email_addrs in one column. what i do now is select distinct on each and then
sort -u the results.

thanks in advance
-t

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Tue, 05 Jul 2005 06:37:52 GMT
 OT: seeking query help, where?

I've never really used this site but it might be of interest:
http://www.dbforums.com/

You may also want to check out "SQL Queries for Mere Mortals"
(Hernandez/Viescas - Addison Wesley)

-Steve


Quote:
> First, sorry for the OT, flame me off-list!

> I'm a sysadmin being impressed into dba service. i've been getting along
> pretty well writing queries and making reports, but i've got some
> questions. suggestions for a good list/forum for help?

> from two tables both with email_addr columns, i want a distinct list of all
> email_addrs in one column. what i do now is select distinct on each and
> then sort -u the results.

> thanks in advance
> -t

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Tue, 05 Jul 2005 07:04:01 GMT
 OT: seeking query help, where?
there are several ways to do this, one example would be:

select distinct(email_addr) from table1
union
select distinct(email_addr) from table2

You can ask questions like these on the pgsql-newbies or pgsql-sql lists
if you'd like (though generally you shouldn't get flamed no matter which
group you post to)

Robert Treat

Quote:

> First, sorry for the OT, flame me off-list!

> I'm a sysadmin being impressed into dba service. i've been getting along
> pretty well writing queries and making reports, but i've got some questions.
> suggestions for a good list/forum for help?

> from two tables both with email_addr columns, i want a distinct list of all
> email_addrs in one column. what i do now is select distinct on each and then
> sort -u the results.

> thanks in advance
> -t

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Tue, 05 Jul 2005 07:15:23 GMT
 OT: seeking query help, where?
This is a multipart message in MIME format.
--=_alternative 007FE7CA86256CB0_=
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

you should be able to do something like:

select distinct email from test1
union (select distinct email from test2);

regards,

Devinder Rajput
Stores Division Corporate Offices
Chicago, IL
(773) 442-6474



01/16/2003 04:15 PM


        cc:
        Subject:        [ADMIN] OT: seeking query help, where?

First, sorry for the OT, flame me off-list!

I'm a sysadmin being impressed into dba service. i've been getting along
pretty well writing queries and making reports, but i've got some
questions.
suggestions for a good list/forum for help?

from two tables both with email_addr columns, i want a distinct list of
all
email_addrs in one column. what i do now is select distinct on each and
then
sort -u the results.

thanks in advance
-t

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

--=_alternative 007FE7CA86256CB0_=
Content-Type: text/html;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

<br><font size=2 face="sans-serif">you should be able to do something like:</font>
<br>
<br><font size=2 face="sans-serif">select distinct email from test1</font>
<br><font size=2 face="sans-serif">union (select distinct email from test2);</font>
<br>
<br><font size=2 face="sans-serif">regards,</font>
<br><font size=2 face="sans-serif"><br>
Devinder Rajput<br>
Stores Division Corporate Offices<br>
Chicago, IL<br>
(773) 442-6474</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>


<p><font size=1 face="sans-serif">01/16/2003 04:15 PM</font>
<br>
<td><font size=1 face="Arial">&nbsp; &nbsp; &nbsp; &nbsp; </font>

<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp; &nbsp; &nbsp;</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; Subject: &nbsp; &nbsp; &nbsp; &nbsp;[ADMIN] OT: seeking query help, where?</font></table>
<br>
<br>
<br><font size=2 face="Courier New">First, sorry for the OT, flame me off-list!<br>
<br>
I'm a sysadmin being impressed into dba service. i've been getting along<br>
pretty well writing queries and making reports, but i've got some questions.<br>
suggestions for a good list/forum for help?<br>
<br>
from two tables both with email_addr columns, i want a distinct list of all<br>
email_addrs in one column. what i do now is select distinct on each and then<br>
sort -u the results.<br>
<br>
thanks in advance<br>
-t<br>
<br>
<br>
---------------------------(end of broadcast)---------------------------<br>
TIP 2: you can get off all lists at once with the unregister command<br>

<br>
</font>
<br>
<br>
--=_alternative 007FE7CA86256CB0_=--



Tue, 05 Jul 2005 07:23:04 GMT
 OT: seeking query help, where?
On Thu, Jan 16, 2003 at 14:15:22 -0800,

Quote:
> First, sorry for the OT, flame me off-list!

> I'm a sysadmin being impressed into dba service. i've been getting along
> pretty well writing queries and making reports, but i've got some questions.
> suggestions for a good list/forum for help?

> from two tables both with email_addr columns, i want a distinct list of all
> email_addrs in one column. what i do now is select distinct on each and then
> sort -u the results.

Use the union operator.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Tue, 05 Jul 2005 07:23:05 GMT
 OT: seeking query help, where?

Quote:

> from two tables both with email_addr columns, i want a distinct list of all
> email_addrs in one column. what i do now is select distinct on each and then
> sort -u the results.

You want either pgsql-sql or pgsql-novice, both of which are mailing
lists for PostgreSQL. The former would be exactly the correct forum.
Odds are someone in this list will answer you anyway with something
mostly-useful.

I think the UNION clause will help you to your eventual goal.

--
Tim Ellis
Senior Database Architect

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Tue, 05 Jul 2005 18:53:33 GMT
 OT: seeking query help, where?
I'd suggest something similar to, but slightly different from, what others
have:

SELECT DISTINCT email FROM (SELECT email FROM table_1 UNION SELECT
        email FROM table_2) AS combined;

that way you avoid duplicates across tables.

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill

Quote:

> First, sorry for the OT, flame me off-list!

> I'm a sysadmin being impressed into dba service. i've been getting along
> pretty well writing queries and making reports, but i've got some questions.
> suggestions for a good list/forum for help?

> from two tables both with email_addr columns, i want a distinct list of all
> email_addrs in one column. what i do now is select distinct on each and then
> sort -u the results.

> thanks in advance
> -t

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly


Tue, 05 Jul 2005 21:58:48 GMT
 OT: seeking query help, where?
On Fri, Jan 17, 2003 at 08:57:14 -0500,

Quote:
> I'd suggest something similar to, but slightly different from, what others
> have:

> SELECT DISTINCT email FROM (SELECT email FROM table_1 UNION SELECT
>    email FROM table_2) AS combined;

> that way you avoid duplicates across tables.

The union operator already removes duplicates.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Tue, 05 Jul 2005 22:59:45 GMT
 OT: seeking query help, where?
Hey, thanks - nice to learn something.

ap

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill

Quote:

> On Fri, Jan 17, 2003 at 08:57:14 -0500,

> > I'd suggest something similar to, but slightly different from, what others
> > have:

> > SELECT DISTINCT email FROM (SELECT email FROM table_1 UNION SELECT
> >       email FROM table_2) AS combined;

> > that way you avoid duplicates across tables.

> The union operator already removes duplicates.

> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate

> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Tue, 05 Jul 2005 23:05:38 GMT
 OT: seeking query help, where?

Quote:

> On Fri, Jan 17, 2003 at 08:57:14 -0500,

> > that way you avoid duplicates across tables.

> The union operator already removes duplicates.

Right, which means everyone's UNION queries have too many DISTINCTs
in them:

SELECT email from table1
UNION
SELECT email from table2 ;

should do it.

Here's proof: note the duplicate 'foo' and 'bar':

test=# select * from table1;
      email      
-----------------



(3 rows)

test=# select * from table2;
      email      
------------------


(2 rows)

test=# select email

test=# select email from table1 union select email from table2;
      email      
------------------



(3 rows)

Ross
--

Research Scientist                                  phone: 713-348-6166
The Connexions Project      http://cnx./rice.edu      fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

---------------------------(end of broadcast)---------------------------



Tue, 05 Jul 2005 23:55:03 GMT
 OT: seeking query help, where?

Quote:

>> The union operator already removes duplicates.
> Right, which means everyone's UNION queries have too many DISTINCTs
> in them:

Relevant to this thread: if you don't want duplicate removal, write
UNION ALL.  This is considerably cheaper than UNION, so it's a good
thing to keep in mind.

Examples:

regression=# explain analyze select unique1 from tenk1 union all select unique2 from tenk1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..916.00 rows=20000 width=4) (actual time=0.20..1416.60 rows=20000 loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.18..549.34 rows=10000 loops=1)
         ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.15..279.58 rows=10000 loops=1)
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.14..548.90 rows=10000 loops=1)
         ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.11..278.33 rows=10000 loops=1)
 Total runtime: 1570.02 msec
(6 rows)

regression=# explain analyze select unique1 from tenk1 union select unique2 from tenk1;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2344.77..2444.77 rows=20000 width=4) (actual time=2881.85..3477.51 rows=10000 loops=1)
   ->  Sort  (cost=2344.77..2394.77 rows=20000 width=4) (actual time=2881.82..3169.29 rows=20000 loops=1)
         Sort Key: unique1
         ->  Append  (cost=0.00..916.00 rows=20000 width=4) (actual time=0.21..1590.55 rows=20000 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.19..570.31 rows=10000 loops=1)
                     ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.15..293.18 rows=10000 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.15..698.43 rows=10000 loops=1)
                     ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=4) (actual time=0.12..419.75 rows=10000 loops=1)
 Total runtime: 3574.98 msec
(9 rows)

The sort-and-unique phases are what implement duplicate removal, and as
you can see they add a good deal to the cost of the query.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Wed, 06 Jul 2005 00:24:33 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. OT: seeking query help, where?

2. HELP w/ Delete Query ot Oracle 7.3

3. Desperately seeking SQL Query solution, Help Please!!

4. HELP: Seeking advice on 3rd party Query tools

5. Help: Seek command won't seek

6. OT Announce: Query Express 2.4 Report Server

7. OT: Help Please shell scripting

8. Stored Procedure ot Trigger Help

9. How to write a help file - slightly OT

10. OT: Help! IIS, SQL 2000, Roles, Users

11. OT: help! Attaching file to an e-mail

12. OT : timestamps and jbuilder (help, please)


 
Powered by phpBB® Forum Software