Last post Oct 14, 2009 02:55 AM by hkbeer
Oct 12, 2009 09:17 PM|hkbeer|LINK
I have a mdb file table with data like this
A, 31 jan 2009
B, 1 jan 2009
C, 31 dec 2008
I want to add a column with ranking such that it becomes
Name, Date, Rank
A, 31 jan 2009,3
B, 1 jan 2009,2
C, 31 dec 2008,1
How to do this by SQL ?
Oct 12, 2009 09:36 PM|vik20000in|LINK
Select column1, columns2, row_number() over (order by Column1 ) as rnk from tablename
Oct 12, 2009 10:10 PM|hkbeer|LINK
Thanks a lot. I pasted this query into Access and it said syntax error.
Oct 13, 2009 02:30 AM|Mikesdotnetting|LINK
You got an error because the Row Number function only works in SQL Server.
Do you want to add a column in Access? Or do you want to actually apply a rank to each item based on its order? If it's the second option, that will not be easy. Every time you add another record, you will have to change the rank potentially for all records.
That's why people use ORDER BY.
Oct 13, 2009 03:55 PM|hans_v|LINK
SELECT [Name], [Date], (SELECT COUNT(*) FROM [tablename] B WHERE [B].[Date] <= [A].[Date]) AS [Rank] FROM [tablename] A ORDER BY [Date] Desc
Note that this will only work when alle dates are unique, and that performance can be poor when dealing with many records!
Oct 14, 2009 02:55 AM|hkbeer|LINK
Yes you are right there is a serious performance issue !!
Thanks anyway now I know this does not work well. I have adopted other approach avoiding creating Ranking.