# Thread: Two Questions; Second Largest and String matching

1. Registered User
Join Date
Dec 2004
Location
Memphis
Posts
25

## 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.

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
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.

6. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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.

7. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered User
Join Date
Dec 2004
Location
Memphis
Posts
25

## 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 ?

9. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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>```
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. Registered User
Join Date
Dec 2004
Location
Memphis
Posts
25

## 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.

11. Registered User
Join Date
Nov 2004
Location
Temple University
Posts
36
Could we not select distinct(sal)....

12. Registered User
Join Date
Dec 2004
Location
Memphis
Posts
25
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 ?

13. Registered User
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
•