how to find max amount

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » how to find max amount

11 posts, Page 1 of 2

1 2 »»

how to find max amount

Rate Topic

Display Mode

Topic Options

Author

Message

varshini

Posted Wednesday, August 18, 2010 9:13 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107

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 ?

Post #971575

Steve Jones - SSC Editor

Posted Wednesday, August 18, 2010 9:41 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

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

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

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #971584

WayneS

Posted Wednesday, August 18, 2010 9:46 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:35 PM
Points: 6,369, Visits: 8,232

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.

Wayne
Microsoft Certified Master: SQL Server 2008

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #971586

varshini

Posted Thursday, August 19, 2010 2:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107

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

Post #971662

varunfilim

Posted Thursday, August 19, 2010 2:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, December 19, 2010 10:52 PM
Points: 30, Visits: 213

Hi,

SELECT Employeeid,max(Amount),receiveddate from table
group by Employeeid,receiveddate. Smile

Varun R
http://www.sqlinfo.in

Post #971663

Kingston Dhasian

Posted Thursday, August 19, 2010 6:54 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 2,221, Visits: 4,184

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/

Post #971831

Subbu S

Posted Wednesday, August 25, 2010 11:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:49 AM
Points: 45, Visits: 138

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

Post #975365

caladba

Posted Thursday, August 26, 2010 10:23 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:18 PM
Points: 97, Visits: 457

You can use ROW_NUMBER() with PARTITION clause.
http://msdn.microsoft.com/en-us/library/ms186734.aspx

Post #975775

TakeITeasy

Posted Friday, August 27, 2010 10:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 8:28 AM
Points: 31, Visits: 52

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,

Post #976574

TakeITeasy

Posted Friday, August 27, 2010 10:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 8:28 AM
Points: 31, Visits: 52

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.

Post #976583

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions