SQL QUERY [message #689343] |
Sat, 25 November 2023 04:40 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/cc837b9c65ed1aec0080f5550741ecfe?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 #689346 is a reply to message #689344] |
Sat, 25 November 2023 05:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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>
|
|
|
|