Home » SQL & PL/SQL » SQL & PL/SQL » need help in query (oracle 11i)
need help in query [message #627076] Fri, 07 November 2014 02:44 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13920
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 Go to previous message
Solomon Yakobson
Messages: 3273
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:27:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.