how to find max amount

Question

Post reply

how to find max amount

varshini

Right there with Babe

Points: 725
More actions
August 18, 2010 at 9:13 pm

#236830

i have the following table
Employeeid Amount ReceivedDate
1 3000 2010-Jan-19
1 4000 2009-Dec-10
1 2500 2009-Mar-25
19 3400 2010-Apr-25
19 8700 2010-Jan-10
27 7700 2009-May-29
27 3400 2006-Jul-20
i need the following output from above table
(employee amount for max receivedDate)
Employeeid Amount ReceivedDate
1 3000 2010-Jan-19
19 3400 2010-Apr-25
27 7700 2009-May-29
how to achive this ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 1

use a max() aggregate and a group by clause.

It's fairly simple, but it almost seems like this is homework. Try it yourself.

WayneS SSC Guru Points: 95460 More actions · Answer 2

I would suggest:

1. Using a Common Table Expression (CTE), get all the values of the table. Utilize the row_number function to assign row numbers to each row, starting at one for each employeeid, and ordering by the date descending, putting this value into a new column (RN).

2. Select the columns from the CTE where the new column RN = 1.

For assistance in figuring out how to use the row_number function, see this article[/url].

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

varshini Right there with Babe Points: 725 More actions · Answer 3

can you expline me how to use the simple query to achive this ?

varunfilim Mr or Mrs. 500 Points: 594 More actions · Answer 4

Hi,

SELECT Employeeid,max(Amount),receiveddate from table

group by Employeeid,receiveddate.:-)

Varun R

http://www.sqlinfo.in

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

varshini (8/19/2010)
can you expline me how to use the simple query to achive this ?

If people start giving you the solutions directly you will not learn. Wayne has given you the correct suggestion. Try some code yourself and if you are stuck somewhere, get back to us with the code that you have tried. We will be more than happy to help you.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Subbu S Old Hand Points: 389 More actions · Answer 6

SELECT EMPNAME,

AMOUNT,

RECIVEDDATE

FROM Table_Name A

WHERE RECIVEDDATE = (SELECT MAX(Reciveddate) FROM Table_Name B WHERE A.RecivedDate = B.RecivedDate)

Plz let me knw, whther this query solved your problem

caladba SSCommitted Points: 1514 More actions · Answer 7

You can use ROW_NUMBER() with PARTITION clause.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

TakeITeasy SSC Enthusiast Points: 181 More actions · Answer 8

Hi ,

Try this script

Declare @t table(id int, amount varchar(10),adate datetime)

insert into @t select 1,'120', dateadd(day,-1,getdate())

insert into @t select 2,'121', getdate()

insert into @t select 1,'122', dateadd(day,-2,getdate())

insert into @t select 4,'123', getdate()

insert into @t select 1,'124', getdate()

insert into @t select 3,'125', dateadd(day,-3,getdate())

insert into @t select 3,'126', getdate()

--------HERE

;WITH cte AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY adate desc) AS ROW,

id,

amount,

adate

FROM @t

)

SELECT *

FROM cte

where Row = 1

Thanks,

TakeITeasy SSC Enthusiast Points: 181 More actions · Answer 9

Hi, Change @t to your table name and your columns for id, amount, adate

WITH cte AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY adate desc) AS ROW,

id,

amount,

adate

FROM @t

)

SELECT *

FROM cte

where Row = 1

this should work fine.

caladba SSCommitted Points: 1514 More actions · Answer 10

Same result as TakeITeasy, but using a derived table.

SELECT*

FROM(

SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY adate desc) AS ROW,

id,

amount,

adate

FROM @t

) T

WHERE T.ROW = 1