Published on: 18 July 2007 By: Ahsan Khan
How to create dynamic row numbers in SQL.
(Solution 1 works in both MS-SQL SERVER 2005 and MY-SQL).
SOLUTION 1SELECT COUNT(*) AS rank, p.id, p.filename
FROM photo p, photo ph
WHERE p.id >= ph.id
GROUP BY p.id, p.filename
ORDER BY rank
SOLUTION 2-- Create a temporary table
CREATE TABLE #rownumber (
rownumber int identity(1,1),
col varchar(80)
)
-- Insert values into temporary table
INSERT #rownumber (col)
SELECT [filename] FROM photo
-- Select all from temporary table
SELECT * FROM #rownumber
GO
-- Drop temporary table when finished
DROP TABLE #rownumber
GO
View All Articles (Articles Archive)