SQL Clone
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
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 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 Guru
SSC Guru (330K reputation)SSC Guru (330K reputation)SSC Guru (330K reputation)SSC Guru (330K reputation)SSC Guru (330K reputation)SSC Guru (330K reputation)SSC Guru (330K reputation)SSC Guru (330K reputation)

Group: Administrators
Points: 330193 Visits: 20112
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
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49382 Visits: 10851
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
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 107
can you expline me how to use the simple query to achive this ?
varunfilim
varunfilim
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

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

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







Varun R
http://www.sqlinfo.in
Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11391 Visits: 5331
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-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

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

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

Group: General Forum Members
Points: 115 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