October 24, 2007 at 8:03 pm
Hi All
I would like to rewrite using group rather than subquery.
select Comments , ao.AffiliateID
from tblAffiliatePaymentComments ao inner join (
select Max(insertDate) as insertDate, AffiliateID
from tblAffiliatePaymentComments
group by AffiliateID
) ai on ao.AffiliateID = ai.AffiliateID and ao.InsertDate = ai.insertdate
It basically get the first record with the latest date. Is there any way of doing this?
Thanks
October 25, 2007 at 12:09 am
Use ROW_NUMBER()....
SELECT*
FROM(
SELECTROW_NUMBER() OVER( PARTITION BY AffiliateID ORDER BY AffiliateID, insertDate DESC ) AS RowNum,
Comments, AffiliateID
FROMtblAffiliatePaymentComments
) ao
WHERERowNum = 1
--Ramesh
October 25, 2007 at 11:07 pm
Thanks for the solution. It works perfectly. What is the function over (Partition BY) does? What is the different between Over(Partition By) and Over(Order by).
October 25, 2007 at 11:47 pm
try out these......against your quieries
What is the function over (Partition BY) in sql server?
https://msdn2.microsoft.com/en-us/library/ms345144.aspx
What is the different between Over(Partition By) and Over(Order by).
http://microsoft.apress.com/feature/68/introduction-to-sql-server-2005-part-2-t-sql
October 26, 2007 at 12:19 am
Catcha (10/25/2007)
Thanks for the solution. It works perfectly. What is the function over (Partition BY) does? What is the different between Over(Partition By) and Over(Order by).
Partition by is like grouping for the row number, order by defines the order for the row number.
Let's take a quick example. Say you have a table with a category and an amount say something like this
CREATE TABLE TestingRowNumber (
category CHAR(1),
amount money
)
GO
INSERT INTO TestingRowNumber VALUES ('a',8.65)
INSERT INTO TestingRowNumber VALUES ('b',0.5)
INSERT INTO TestingRowNumber VALUES ('a',7.14)
INSERT INTO TestingRowNumber VALUES ('a',93.2)
INSERT INTO TestingRowNumber VALUES ('c',1.02)
INSERT INTO TestingRowNumber VALUES ('b',0)
INSERT INTO TestingRowNumber VALUES ('b',7.25)
INSERT INTO TestingRowNumber VALUES ('a',4.25)
Now, rownumber over partition by category will give you row numbers 1-4 for category a, 1-3 for category b and 1 for category c.
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY category) FROM TestingRowNumber -- the order by is required
If we remove the partition by, then you get one run of numbers, with category a having 1-4, b having 5-7 and c having 8
SELECT *, ROW_NUMBER() OVER (ORDER BY category) FROM TestingRowNumber
Make sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply