SQL: Numbering rows in a temporary column?

The need to see them numbered sounds like more of a front-end concern. Creating a temporary column in the database is a stretch for something like this. Remember that your result set is the product of tables + joins + selection criteria, so it's not even a "table" per se, it's the product of some tables + your query. Creating a column doesn't make sense. Which table would it go in? You'd need to create a temporary "query results" table, but this is what front ends are for. I think ordering your output rows should be reasonably doable in your front end app, though I know nothing about Access.

You can do it in PostgreSQL (and Oracle) with http://microolap.com/dba/pgsql/isql/help/pghelp/sql-createsequence.html In MS SQL you can mimic the behaviour of sequences by producing your own procedure to produce numbers.. http://jamesthornton.com/software/coldfusion/nextval.html This is not a full solution by any means, but should be hackable. As I have zero knowledge of MS's scary system, I cannot help with this, but I figure you can hack it into returning a collection of values, and use it in a JOIN with your main query. Best of luck!

After following vacapinta's link, I'd say that provides a good way to do it, doesn't seem tricky to me, although very MS SQL specific.. so give that a shot first.

If you just want to count the number of results for a query, what's the matter with http://www.techonthenet.com/sql/count.htm%20 ? Obviously you can do sequences and such, but this seems like the most direct approach.

(Oh. I misread the question. Shutting up now.)

Remember that your result set is the product of tables + joins + selection criteria, so it's not even a "table" per se, it's the product of some tables + your query. With all due respect, this is not a productive way of thinking in SQL. With structures like views and nested queries, a result set is a table in many ways. I was also going to add that the auto-join mentioned in the sql server link applies to Oracle and other SQL databases and is a powerful way of getting things done. For example, counting duplicate rows in a table is done the same way: Select count(*) from nametable a where userid>(select min(userid) from nametable b where a.first_name=b.first_name); For userid, you can subsitute any unique, ordered column. This example counts how many duplicate first names there are in the table.

Finally a question I feel comfortable answering... Here is a quick query I wrote up that accomplishes what you want, you can put it in a SQL Server stored procedure to use it . (sample codes creates and orders a numbered list of users who are employees ordered by name) SET NOCOUNT ON CREATE TABLE #temp ( Counter INTEGER IDENTITY (1,1) NOT NULL, UserID INTEGER ) INSERT INTO #temp (UserID) SELECT UserID FROM Users WHERE Users.UserID IN (SELECT UserID FROM Employees) ORDER BY Users.LastName, Users.FirstName SELECT #temp.Counter, Users.* FROM #temp INNER JOIN Users ON Users.UserID=#temp.UserID ORDER BY #temp.Counter DROP TABLE #temp Of course, change the tables to accomplish what you want, but I think this is a fairly good template.