Solved

SQL Query

Posted on 2011-02-15
4
363 Views
Last Modified: 2012-05-11
Hi,

I have a following  table

consumerno                   billdate                   billamount
54321                              01/01/2011          100
54322                               15/01/2011            50
54321                               02/02/2011              175
54322                               03/02/2011             100


i want a query that will give me bill amount for a consumerno with latest bill date.

i,.e.
consumerno                billdate                           billamount
54321                             02/02/2011                   175
54322                              03/02/2011                  100



Please Help
0
Comment
Question by:santoshmotwani
  • 2
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34903102
If this is SQL Server 2005+ then you can use row_number()

select consumerno, billdate, billamount
from
(
select *, row_number() over (partition by consumerno order by billdate desc) as rownum
from tbl
) SQ
where rownum=1
0
 
LVL 16

Author Comment

by:santoshmotwani
ID: 34903138
great ,

what if i want total billamount ?


Thnx for your help
0
 
LVL 16

Author Closing Comment

by:santoshmotwani
ID: 34903171
thnx !!!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34909483
>what if i want total billamount ?

In that case, for the exact 3 columns required, a simply group by would suffice.

select consumerno, max(billdate) billdate, sum(billamount) totalamount
from tbl
group by consumerno
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question