OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL QUERY [message #689343]

Sat, 25 November 2023 04:40

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

John Watson
Messages: 8931
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?

Report message to a moderator

Re: SQL QUERY [message #689345 is a reply to message #689344]

Sat, 25 November 2023 04:55

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

I am using 11g

Report message to a moderator

Re: SQL QUERY [message #689346 is a reply to message #689344]

Sat, 25 November 2023 05:01

John Watson
Messages: 8931
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>

Report message to a moderator

Re: SQL QUERY [message #689347 is a reply to message #689346]

Sat, 25 November 2023 05:12

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

Thanks John

Report message to a moderator

Previous Topic:	PL/SQL wrapper error
Next Topic:	Sequentially update a column with a decrementing value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 13:03:50 CDT 2024