The company decided to arrange an exchange of experience between employees. To do this, every day one of the employees makes a report. Employees appear in alphabetical order. After all the staff members made a report, the first employee becomes a speaker. Create a query that returns the name of the speaker, if you know the name of the previous speaker.

Actually let the table Emp consist only of the field ename - the name of the employee. It is not so important. I thought that you can glue it twice and bring it together with the rownum.

SELECT ename, rownum FROM( SELECT ename, rownum FROM(SELECT ename FROM Emp ORDER BY ename) UNION ALL SELECT ename, rownum FROM (SELECT ename FROM Emp ORDER BY ename) );

Then it seems to me logical to ask the user for a name, find the first occurrence of this name in the table, take its rownum, increase by 1 and select the desired name from the table. But how to add it to this query, I'm already confused.

    4 answers 4

    In order to find the next speaker, you can sort the full list and then take a record from it, as suggested by @msi, but there is a much more economical option that will use the index by the ename field:

     select nvl( (select min(ename) from emp where ename > :input_name), (select min(ename) from emp ) ) another_next_for_last from dual;

    With the help of the index, the following is determined and, in case it is not, the first one by the same index.

    You can compare options in this SQLFiddle .

    • Thank you, I could not even think that you can use nvl so beautifully! - nulpatrol
     select * from tablename where upper(name) < upper(knownName) order by upper(name) desc limit 1;
    • one
      And unless for A, B at the last A does not return empty result? - BOPOH
     SELECT ( SELECT rownum ename FROM emp WHERE rownum = e.rownum+1 ORDER BY ename ) FROM ( SELECT rownum, ename FROM emp ORDER BY ename ) e WHERE e.ename = 'имя выступающего, лучше конечно еслиб было поле-ID'
    • oh I apologize drowsiness attacked))) in the main SELECT only ENAME should be removed from the subquery))) and then the error of multiple lines will be IvanZakirov
    • Or even try SELECT (SELECT e1.ename FROM e e1 WHERE e1.rownum = e.rownum + 1) FROM ... and then as in the answer))) - IvanZakirov
    • although I am already sleepy can carry nonsense in the second case. But in my opinion rely on ROWNUM is not worth it, because there can be row orders different in subqueries ... and maybe WHERE cuts them before they are numbered, which is likely. - IvanZakirov
     select ename, coalesce( lead(ename) over(order by ename), min(ename) over(order by ename) ) from Emp order by ename; with t as (select ename, coalesce(lead(ename) over(order by ename), min(ename) over(order by ename)) enext from Emp) select enext from t where ename='ThinkJet';
    • The query will return all the records in the table, and you only need the name of the following: "returns the name of the speaker, if the name of the previous speaker is known." Accordingly, you need to call the field and add an external query. - ThinkJet
    • I do not know the previous one. Add it to the WHERE clause, and then you will know the name of the next .. - msi
    • It was a quotation from the statement of the problem in question: "returns the name of the speaker, if the name of the previous speaker is known". Accordingly, if we describe the conditions of the problem, then it will be "Given: Name of the speaker. Find: Name of the speaker following him". - ThinkJet 4:08 pm
    • If you want me to write a query for a specific name, call it and I will write. - msi
    • Yes, it would be good, because, perhaps, your answer will suit someone and it would be better to have a ready-made formulated solution without having to modify it according to the information from the comments. I can fix it with your permission, if for some reason the correction on your part is not possible ... - ThinkJet