Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to find max amount Expand / Collapse
Author
Message
Posted Wednesday, August 18, 2010 9:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Wednesday, August 18, 2010 9:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,355, Visits: 15,819
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
Posted Wednesday, August 18, 2010 9:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 5,370, Visits: 9,008
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
Posted Thursday, August 19, 2010 2:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, August 19, 2010 2:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.







Varun R
http://www.sqlinfo.in
Post #971663
Posted Thursday, August 19, 2010 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
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
Posted Wednesday, August 25, 2010 11:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, August 26, 2010 10:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 1, 2014 12:47 PM
Points: 97, Visits: 560
You can use ROW_NUMBER() with PARTITION clause.
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Post #975775
Posted Friday, August 27, 2010 10:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, August 27, 2010 10:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 12»»

Permissions Expand / Collapse