Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY (11g)
SQL QUERY [message #689343] Sat, 25 November 2023 04:40 Go to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Create Table Employee (EMPLOYEE_NUMBER varchar2(5),EMPLOYEE_NAME VARCHAR2(250),EMP_Date Date)
Insert into Employee Values('20005','John','01-JAN-2023');
Insert into Employee Values('20005','John Martain','02-JAN-2023');
Insert into Employee Values('20005','Johnes','03-JAN-2023');

Insert into Employee Values('20006','Ricky','01-JAN-2023');
Insert into Employee Values('20006','Ricky Martain','02-JAN-2023');
Insert into Employee Values('20006','Rickyes','03-JAN-2023');


Select EMPLOYEE_NAME, EMPLOYEE_NUMBER from Employee;

My Data appears as follows
Jones 20005       01-JAN-2023
John Martain 20005 02-JAN-2023
Johnes   20005 03-JAN-2023
Ricky 20006 01-JAN-2023
Ricky Martain 20006 02-JAN-2023
Rickyes   20006 03-JAN-2023

I want to  query  which employee has highest length in name
for examle employee number 20005 highest lenth is John Martain and in second employee number
20006 is Ricky Martain

Ricky Martain 20006 02-JAN-2023
John Martain 20005 02-JAN-2023


I tried this query

SELECT *
FROM EMPLOYEE
WHERE LENGTH(EMPLOYEE_NAME) = (
SELECT MAX(LENGTH(EMPLOYEE_NAME))
FROM EMPLOYEE a
where EMPLOYEE_NUMBER=a.EMPLOYEE_NUMBER )

But only one record is appearing
20006 Ricky Martain 02-JAN-2023

[Updated on: Sat, 25 November 2023 04:43]

Report message to a moderator

Re: SQL QUERY [message #689344 is a reply to message #689343] Sat, 25 November 2023 04:49 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
Firstly, you have been asked numerous times to use tags to format your posts. It is so rude to repeatedly ignore the forum etiquette in this way.

Secondly, your test case is buggy:
orclz>
orclz> Create Table Employee (EMPLOYEE_NUMBER varchar2(5),EMPLOYEE_NAME VARCHAR2(250),EMP_Date Date);

Table created.

orclz> Insert into Employee Values('20005','John','01-JAN-2023');

1 row created.

orclz> select * from  Employee;

EMPLO EMPLOYEE_NAME                  EMP_DATE
----- ------------------------------ -------------------
20005 John                           0001-01-20:23:00:00

orclz>
(note my use of [code] tags) did you really want emp_date to be eleven o'clock in the evening on the twentieth of January year one? If not, you need to think about data types.

Thirdly, what SQL have you tried so far?
Re: SQL QUERY [message #689345 is a reply to message #689344] Sat, 25 November 2023 04:55 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
I am using 11g
Re: SQL QUERY [message #689346 is a reply to message #689344] Sat, 25 November 2023 05:01 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
Ah! I see that you have added a query. Well done. Pity it isn't properly tagged. I'll give you a hint, none the less. I would begin with this,
orclz> select employee_number,max(length(employee_name)) from employee group by employee_number;

EMPLO MAX(LENGTH(EMPLOYEE_NAME))
----- --------------------------
20006                         13
20005                         12

orclz>
Re: SQL QUERY [message #689347 is a reply to message #689346] Sat, 25 November 2023 05:12 Go to previous message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Thanks John
Previous Topic: PL/SQL wrapper error
Next Topic: Sequentially update a column with a decrementing value
Goto Forum:
  


Current Time: Thu Feb 22 14:45:40 CST 2024