Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to find max amount


how to find max amount

Author
Message
varshini
varshini
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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 ?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36095 Visits: 18738
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
My Blog: www.voiceofthedba.com
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
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
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, 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

varshini
varshini
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 107
can you expline me how to use the simple query to achive this ?
varunfilim
varunfilim
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 213
Hi,

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







Varun R
http://www.sqlinfo.in
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3002 Visits: 5000
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
Subbu S
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
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
caladba
caladba
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 637
You can use ROW_NUMBER() with PARTITION clause.
http://msdn.microsoft.com/en-us/library/ms186734.aspx
TakeITeasy
TakeITeasy
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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,
TakeITeasy
TakeITeasy
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search