OraFAQ Forum: SQL & PL/SQL » need help in query

Home » SQL & PL/SQL » SQL & PL/SQL » need help in query (oracle 11i)

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

need help in query [message #627076]

Fri, 07 November 2014 02:44

moulivasireddy
Messages: 2
Registered: November 2014

Junior Member

Input

I need sql query for below scneario

PLease find sample data . And i having 10000 employee information . here i took only few records.

I just employee in which earned more in a month max(EMPMONSAL)

EMPID EMPNAME EMPMONSAL MONTH YEAR
10 john 100 JAN 2013
10 john 200 FEB 2013
20 PAPA 400 JAN 2013
20 PAPA 300 FEB 2013
20 PAPA 500 MAR 2013
30 JACK 888 JAN 2013
30 JACK 777 FEB 2013

Ouput ::

I want month in which employee earned more in a month

EMPNAME MAXSAL MONTH YEAR
JOHN 200 FEB 2013
PAPA 500 MAR 2013
JACK 888 JAN 2013

Report message to a moderator

Re: need help in query [message #627085 is a reply to message #627076]

Fri, 07 November 2014 03:06

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and at http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post.
Once you have numbered your rows per empid then you just have to return those with rank 1.

Report message to a moderator

Re: need help in query [message #627094 is a reply to message #627085]

Fri, 07 November 2014 03:24

moulivasireddy
Messages: 2
Registered: November 2014

Junior Member

Can we do by using subquery/Corealted query to achieve result.

Report message to a moderator

Re: need help in query [message #627096 is a reply to message #627094]

Fri, 07 November 2014 03:30

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Did you read the link I provided?

Report message to a moderator

Re: need help in query [message #636896 is a reply to message #627096]

Tue, 05 May 2015 04:09

browncat
Messages: 9
Registered: May 2015

Junior Member

SELECT EMPNAME, MAX(EMPMONSAL), MONTH, YEAR FROM <Table-name> GROUP BY MONTH;

Report message to a moderator

Re: need help in query [message #636898 is a reply to message #636896]

Tue, 05 May 2015 04:30

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's the second time today you've posted an invalid group by.
While we appreciate the fact that you're trying to help you really need to slow down and think a bit more about what you post.

Report message to a moderator

Re: need help in query [message #636905 is a reply to message #627076]

Tue, 05 May 2015 06:35

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel pointed you to analytic solution. Aggregate solution would be GROUP BY + FIRST/LAST.

SY.

Report message to a moderator

Previous Topic:	alignment of output in the dbms_output file
Next Topic:	How to avoid showing data on each row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 29 04:09:07 CDT 2026