SQL: Numbering rows in a temporary column?
July 19, 2004 11:01 PM   Subscribe

SQL Filter: is there any way when making a query to create a temporary column that assigns each row in the result set an ordinal number? This is for when you not only want to count the number of items in a result set (ie, there are 50 items), you also want them numbered 1-50 or whatever.

I'm using Access as a client for SQL Server, FWIW, but the more general the SQL answer, the better.
posted by wildblueyonder to Computers & Internet (8 answers total)
I dont believe there is a standard SQL way. Oracle uses a pseudo-column called 'rownum'. Its a bit trickier in SQL Server but there are ways to do it.
posted by vacapinta at 11:30 PM on July 19, 2004

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.
posted by scarabic at 12:06 AM on July 20, 2004

You can do it in PostgreSQL (and Oracle) with sequences.

In MS SQL you can mimic the behaviour of sequences by producing your own procedure to produce numbers.. check this out for clues. 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!
posted by wackybrit at 3:23 AM on July 20, 2004

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.
posted by wackybrit at 3:25 AM on July 20, 2004

If you just want to count the number of results for a query, what's the matter with select count(*) as result_count from table where ... ?

Obviously you can do sequences and such, but this seems like the most direct approach.
posted by majick at 7:42 AM on July 20, 2004

(Oh. I misread the question. Shutting up now.)
posted by majick at 7:44 AM on July 20, 2004

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.
posted by vacapinta at 9:30 AM on July 20, 2004

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)



FROM Users
WHERE Users.UserID IN (SELECT UserID FROM Employees)
ORDER BY Users.LastName, Users.FirstName

SELECT #temp.Counter,
FROM #temp
INNER JOIN Users ON Users.UserID=#temp.UserID
ORDER BY #temp.Counter


Of course, change the tables to accomplish what you want, but I think this is a fairly good template.
posted by patrickje at 10:05 AM on July 20, 2004

« Older On-line gift-shopping ideas   |   My iBook gets hot on the left side. What can I do? Newer »
This thread is closed to new comments.