RE: Is this SQL Possible??

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Mon, 20 Dec 2004 20:47:07 +0100
Message-Id: <20041220194711.CA6F98DA3B1@ha-smtp1.tiscali.nl>

Ben,
if you want to pick any random row, you should consider using and additional "... and rownum = 1" predicate,
whatever your SQL statement is. the Oracle optimizer knows how to handle such constructs, and typically comes back with a reasonable plan.

hope this helps,

Lex.

Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ben Sauer
Sent: Monday, December 20, 2004 20:11
To: Oracle-l_at_freelists.org
Subject: Is this SQL Possible??

I have a many to one relationship from table b to table a. In other words, in
table A I have people and in table B I have assignments. I need to write a query to pick up a set of users in table A and just one of their assignments.
I
don't even care which one. Is there a way to do that elegantly?

I thought this would work... but it's just too time consuming.

SELECT *
FROM ss,

       (SELECT "Support Staff ID", "Group ID"
          FROM gd WHERE "Full Name" IN (SELECT DISTINCT "Full Name"
                                          FROM gd))  WHERE ss.code =

"Support Staff ID"
Thanks,
Ben.

--

This message may contain confidential information, and is intended only for the use of the individual(s) to whom it is addressed.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 20 2004 - 13:46:53 CST