Re: Sorting numbers in a text field

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 29 Jan 2007 11:10:13 -0800
Message-ID: <1170097809.316512@bubbleator.drizzle.com>

spudtheimpaler_at_gmail.com wrote:
>
> On Jan 23, 5:33 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> spudtheimpa..._at_gmail.com wrote:
>>> DA Morgan wrote:
>>>> spudtheimpa..._at_gmail.com wrote:
>>>>>> So I don't think this is the correct answer. The correct answer is to
>>>>>> the run a CREATE DATABASE script, start all over again, and learn to do
>>>>>> things *properly*.
>>>>>> --
>>>>>> Sybrand Bakker
>>>>>> Senior Oracle DBA
>>>>> Thanks, and I have to agree if it were my database it would have been
>>>>> different. I didn't mention (because at the time it didn't seem
>>>>> relevant) that I am in the process of learning SQL, and this was one of
>>>>> a set of questions set by my trainer.
>>>> Get a different trainer: Seriously!
>>>> The only way my students would see this problem would be if the question
>>>> they were being asked was: "What is wrong with this design."
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor..._at_x.washington.edu
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users Group
>>>> www.psoug.org
>>> Lol.
>>> Well as I'm sure you can understand, thats not really possible. but
>>> it's nice to think that when I'm not doing so well it might not just be
>>> my own fault ;)
>>> It's a Java training course I'm actually on, but apparently SQL is a
>>> good selling point so you learn some of that too.
>>> Cheers for you input anyway guys!One of the most fascinating things about our industry is that those that
>> write SQL and PL/SQL ... know they don't know Java.
>>
>> But those that know Java are convinced they understand databases. Some
>> of the worst schema designs I have ever seen were put together by well
>> meaning, and hopelessly ignorant, Java developers.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> Thats interesting. I've not been in the field long enough to know, and
> with utmost respect I have yet to find a language is like/am capable
> of as little as PL/SQL. Maybe I don't like because I don't get, or
> vice versa, but either way I can never see myself being a DBA. We
> don't get along.
>
> Still, Java developers thinking they know SQL, something I will look
> out for in my travels.
>
> Thanks again!
>
> Micth.

My rant isn't about PL/SQL vs Java as languages. You've undoubtedly heard it said that you can't make something idiot proof because they keep making better idiots. Well Java coders meet that definition when they get into the database because they are seemingly incapable of getting past the language to what really matters. Let me give you an example.

Here are 7 SQL statements that produce the exact same result set (given my particular demo data)

SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

   SELECT srvr_id
   FROM servers
   MINUS
   SELECT srvr_id
   FROM serv_inst);

SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

WITH q AS (

   SELECT DISTINCT s.srvr_id
   FROM servers s, serv_inst i
   WHERE s.srvr_id = i.srvr_id)
SELECT * FROM q;

SELECT srvr_id
FROM (
   SELECT srvr_id, SUM(cnt) SUMCNT
   FROM (

     SELECT DISTINCT srvr_id, 1 AS CNT
     FROM servers
     UNION ALL
     SELECT DISTINCT srvr_id, 1
     FROM serv_inst)

   GROUP BY srvr_id)
WHERE sumcnt = 2;

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id+0 = i.srvr_id+0;

I've yet to meet the Java developer capable of writing them much less caring to understand why some are good and some are not. They just write SELECT *, use EJBs, and then expect the DBA to fix what is slow.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jan 29 2007 - 13:10:13 CST