Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Unanswered: Two Questions; Second Largest and String matching

    Hi there !

    I've two question in which I need some assistance. Questions are as follows:

    Ques: How one can find the second, 4th, 6th largest from a given set of list(4000 rec)?

    I know one way to find the same but it's taking lots of time to shows the result. I can find all of them by using Analytical Functions. Is there any other way to do so ? If yes, then please let me know.

    Ques2: I need to list all the employee name who has atleast one small alphabets in their name. For instance, Name like KiDKID, kIDKID,KIdKID, KidKID etc all should show in that list.

    Any suggestion are most welcome.
    NewKid

    A Newkid in the block

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Possible answers could be this two:
    Code:
    DECLARE
      cnt NUMBER(4) := 1;
    BEGIN
      FOR cur_row IN (SELECT empno, ename, sal FROM EMP
                      ORDER BY sal DESC)
      LOOP
         IF cnt = 2 OR cnt = 4 OR cnt = 6 
    	 THEN
    	    dbms_output.put_line(cnt || '. ' || cur_row.ename ||', sal. ' || cur_row.sal);
         END IF;
         cnt := cnt + 1;
         exit when cnt > 6;
      END LOOP;
    END;
    /
    Code:
    DECLARE
      CURSOR cur IS SELECT name FROM MY_TABLE;
      name_step NUMBER(4);
    BEGIN
      FOR cur_row IN cur
      LOOP
         <<step>>
         FOR name_step IN 1 .. LENGTH(cur_row.name)
    	 LOOP
    	    IF SUBSTR(cur_row.name, name_step, 1) <> UPPER(SUBSTR(cur_row.name, name_step, 1))
    		THEN
    		   dbms_output.put_line(cur_row.name);
    		   EXIT step;  -- this allows you to show only distinct names
    		END IF;
         END LOOP;
      END LOOP;
    END;
    /

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    4k records is very tiny for Oracle.

    As for #2, how about something like.. select * from emp where ename <> upper( ename )

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    LOL, JMartinez! Where were you when I was trying to figure that one out?!? Gosh, still have SO much to learn (and use my head)!

  5. #5
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Thanks !

    Thanks JMartinez and Littlefoot !

    I have one question for littlefoot, Will Question 1 reply works in the case when column have same values ? Say, there are 2 values qualifies for the 2nd largest in that list. Will it works in that case also ?

    Thanks JMartinez, I couldn't think 'Upper' functions yesterday..

    Also, I have one new question. That is, How one can remove the white (blank) space between two words? Say, I want to remove the space between 'kid kid' and then shows the new removed list.

    Thanks you all for your time and co-operation.
    NewKid

    A Newkid in the block

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by kidkid
    Thanks JMartinez and Littlefoot !

    Also, I have one new question. That is, How one can remove the white (blank) space between two words? Say, I want to remove the space between 'kid kid' and then shows the new removed list.

    Thanks you all for your time and co-operation.
    Code:
    SQL> select replace('now is the time',' ') from dual;
    
    REPLACE('NOW
    ------------
    nowisthetime
    
    SQL>
    In otherwards, replace all spaces with null.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    1st query will work (that is, won't fail or refuse to compile), but I guess you wouldn't be happy with the result. As you can see, it orders fetched data by the "sal" column, so - if there were (for example) six employees with the same salary, you'd get different names but same salaries.

    However, your request was to select 2nd, 4th and 6th largest from the list. So, if we presume that you want ONE value (salary, not both the name and the salary), modified query would be
    Code:
    DECLARE
      cnt NUMBER(4) := 1;
    BEGIN
      FOR cur_row IN (SELECT DISTINCT sal FROM EMP
                      ORDER BY sal DESC)
      LOOP
         IF cnt = 2 OR cnt = 4 OR cnt = 6 
    	 THEN
    	    dbms_output.put_line(cnt || '. ' || ', sal. ' || cur_row.sal);
         END IF;
         cnt := cnt + 1;
    	 EXIT WHEN cnt > 6;
      END LOOP;
    END;
    /
    As for removing space, you could use this code:
    Code:
    SELECT REPLACE('kid kid', ' ', '') FROM dual;
    Note that only single quotes are used here.

    P.S. Sorry ... didn't see Bill's answer; both were written at the same time
    Last edited by Littlefoot; 12-15-04 at 10:27.

  8. #8
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Thanks !

    No Littlefoot that is not the case..

    It appears to me that your first query answer is similar to the SQL Statement which I was trying earlier.. That is..

    Say, for the case of second highest :

    Select a.sal from emp a where 1 = (Select count(*) from emp b where b.sal > a.sal)
    This query works fine. It also works fine for 4 largest viz..

    Select a.sal from emp a where 3 = (Select count(*) from emp b where b.sal > a.sal)
    Mission accomplished... No it's not.. .Query fails when we have few comman values in the list. For instance, presume employee table sal column values are as follows:

    5000
    4000 <--- 2 highest
    3000 <-- 3 highest
    3000
    2975 <-- 4 highest
    2850
    2450
    1600
    1500
    1300

    In this case, 4th largest query shows "No rows selected"..

    Hope, am making sense ?
    NewKid

    A Newkid in the block

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    According to your example, searching for the 2nd, 3rd and 4th highest salary (didn't make any other changes to the code):
    Code:
    SQL> select sal from empl order by sal desc;
    
           SAL
    ----------
          5000
          4000
          3000
          3000
          2975
          2850
          2450
          1600
          1500
          1300
           800
    
    11 rows selected.
    
    SQL> DECLARE
      2    cnt NUMBER(4) := 1;
      3  BEGIN
      4    FOR cur_row IN (SELECT DISTINCT sal FROM EMPL
      5                    ORDER BY sal DESC)
      6    LOOP
      7       IF cnt = 2 OR cnt = 3 OR cnt = 4
      8      THEN
      9         dbms_output.put_line(cnt || '. ' || 'sal. ' || cur_row.sal);
     10       END IF;
     11       cnt := cnt + 1;
     12      EXIT WHEN cnt > 6;
     13    END LOOP;
     14  END;
     15  /
    2. sal. 4000
    3. sal. 3000
    4. sal. 2975
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Your query requires modification ...
    Code:
    SQL> SELECT a.sal
      2  FROM EMPL a
      3  WHERE 3 = (SELECT COUNT(DISTINCT sal)
      4             FROM EMPL b
      5                WHERE b.sal > a.sal);
    
           SAL
    ----------
          2975
    
    SQL>

  10. #10
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Thanks you all..

    Thanks Littlefoot for corrections. I've to go long mile..

    Start from the scratch and reach to top..

    Thanks you all for your time and co-operations.
    NewKid

    A Newkid in the block

  11. #11
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36

    Cool

    Could we not select distinct(sal)....

  12. #12
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post

    I guess, this is the most correct answer in all respects about how to find the second largest in the list?

    Select Max(Sal) "Second Highest" from emp where sal < (Select Max(Sal) from emp)

    What you all say ?
    NewKid

    A Newkid in the block

  13. #13
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I know one way to find the same but it's taking lots of time to shows the result. I can find all of them by using Analytical Functions. Is there any other way to do so?
    What was the query using analytic functions that ran slowly? I've always found then to be pretty efficient. e.g:
    Code:
    SELECT DISTINCT rn, sal
    FROM   ( SELECT sal
                  , DENSE_RANK() OVER (ORDER BY sal DESC) AS rn
             FROM   empl )
    WHERE rn IN (2,3,6)
    ORDER BY rn;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •