Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query find out the bottom two date against each a_id
SQL Query find out the bottom two date against each a_id [message #434238] Tue, 08 December 2009 01:37 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi,

Please help against the below input and output, I need to sum of amount against the each a_id for the bottom two dates.


A_ID d_DATE AMT
1 10/07/08 352
1 11/07/08 2794
2 12/07/08 1951
2 01/07/09 1326
3 02/07/09 889
3 03/07/09 1684
3 04/07/09 2099
3 05/07/09 1684


OUTPUT (Sum of the amount for the Bottom 2 date against the each a_id)
1 3146
2 3276
3 2572


Regards,
Arvind Kumar
Re: SQL Query find out the bottom two date against each a_id [message #434241 is a reply to message #434238] Tue, 08 December 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions to get the 2 dates and then use classic GROUP BY the sum on the select rows.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

And post a working Test case with your question: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: SQL Query find out the bottom two date against each a_id [message #434257 is a reply to message #434241] Tue, 08 December 2009 03:27 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
we have tried but not able to extract prper data please help

select a_id,d_date,sum(amt), RANK() OVER (ORDER BY invoice_date DESC) invoice_rank from xxxx
where a_id in (1159,278210,1420)
group by a_id,d_date
order by 1

Re: SQL Query find out the bottom two date against each a_id [message #434260 is a reply to message #434257] Tue, 08 December 2009 03:39 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to rank in a subquery and group in an outer one.

Regards
Michel
Previous Topic: ORA-06502 issue after 10g migration
Next Topic: Query on PL/SQL logic
Goto Forum:
  


Current Time: Fri Dec 09 11:51:28 CST 2016

Total time taken to generate the page: 0.09500 seconds