InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 05:30:21 GMT20http://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxSame 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 ) TWHERE T.ROW = 1Fri, 27 Aug 2010 11:44:09 GMTcaladbahttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxHi, 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 ctewhere Row = 1this should work fine.Fri, 27 Aug 2010 10:52:05 GMTTakeITeasyhttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxHi ,Try this scriptDeclare @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 ctewhere Row = 1Thanks,Fri, 27 Aug 2010 10:39:44 GMTTakeITeasyhttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxYou can use ROW_NUMBER() with PARTITION clause.[url=http://msdn.microsoft.com/en-us/library/ms186734.aspx]http://msdn.microsoft.com/en-us/library/ms186734.aspx[/url]Thu, 26 Aug 2010 10:23:29 GMTcaladbahttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxSELECT EMPNAME, AMOUNT, RECIVEDDATEFROM Table_Name AWHERE RECIVEDDATE = (SELECT MAX(Reciveddate) FROM Table_Name B WHERE A.RecivedDate = B.RecivedDate)Plz let me knw, whther this query solved your problemWed, 25 Aug 2010 23:37:40 GMTSubbu Shttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspx[quote][b]varshini (8/19/2010)[/b][hr]can you expline me how to use the simple query to achive this ?[/quote]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.Thu, 19 Aug 2010 06:54:09 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxHi,SELECT Employeeid,max(Amount),receiveddate from tablegroup by Employeeid,receiveddate.:-)Varun Rhttp://www.sqlinfo.inThu, 19 Aug 2010 02:09:50 GMTvarunfilimhttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxcan you expline me how to use the simple query to achive this ?Thu, 19 Aug 2010 02:02:19 GMTvarshinihttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxI 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 [url=http://www.sqlservercentral.com/articles/T-SQL/69717/][u]this article[/u][/url].Wed, 18 Aug 2010 21:46:26 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxuse a max() aggregate and a group by clause.It's fairly simple, but it almost seems like this is homework. Try it yourself.Wed, 18 Aug 2010 21:41:40 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic971575-391-1.aspxi have the following tableEmployeeid Amount ReceivedDate1 3000 2010-Jan-191 4000 2009-Dec-101 2500 2009-Mar-2519 3400 2010-Apr-2519 8700 2010-Jan-1027 7700 2009-May-2927 3400 2006-Jul-20i need the following output from above table(employee amount for max receivedDate)Employeeid Amount ReceivedDate1 3000 2010-Jan-1919 3400 2010-Apr-2527 7700 2009-May-29how to achive this ?Wed, 18 Aug 2010 21:13:21 GMTvarshini