generate row number dynamically with query?

[sneakyimp]

I'm wondering if it might be possible to run a query that joins a couple of databases but also numbers each row. something like:

select BLAH() as row_number, t1.foo, t2.bar FROM table1 t1, table2 t2

which would return results like

1   record1foo   record1bar
2   record2foo   record2bar
3   record3foo   record3bar

Is this possible?

[dagon]

SELECT @row :=0;# Rows: 1
SELECT @row := @row +1 AS rowNumber, t1.foo, t2.bar FROM table1 t1, table2 t2

[Roger Ramjet]

Originally Posted by dagon

SELECT @row :=0;# Rows: 1
SELECT @row := @row +1 AS rowNumber, t1.foo, t2.bar FROM table1 t1, table2 t2

In a stored procedure of course: and looking at your syntax, in an MS SQL database as well.

For the method to do this all in 1 query in any dialect of sql have a read of my blog

[dagon]

Originally Posted by Roger Ramjet

In a stored procedure of course: and looking at your syntax, in an MS SQL database as well.

Actually it was from the mysql forums. And tested before posting.

[Roger Ramjet]

Originally Posted by dagon

Actually it was from the mysql forums. And tested before posting.

Just the @: usual for sql server but not so much for others

[sneakyimp]

Thanks for the help. I've run into this problem several times before. This does in fact work for me in mysql 4 and 5:

SET @row =0;
SELECT @row := @row +1 AS row_number, c.customer_id, u . *
FROM users u
LEFT JOIN customers c ON c.user_id = u.user_id
WHERE c.user_id IS NULL ;

I can find user records that have no associated customer records and give them a sequence number.

Roger, I'm very curious about your approach and was having a bit of trouble figuring out how to apply it when I've already got a join on two tables when it occurred to me that this might be a pretty inefficient query if I have large numbers of records. Seems to me the COUNT bit has to be run for each id.

THANKS both of you, btw.

[Roger Ramjet]

Yes, sneakyimp, it does.

The purpose of my approach is to solve problems that can not be done any other way: eg

delete all but the last 5 posts in every thread/category/sub-set

display the first 3 posts in each thread in all forums

Sequence complex grouping of an arbitrary number of nested subsets stored as hiearchical data (which is what I was solving when I devised this method).

That is, my method is devised to solve those problems that can not be solved by LIMIT (even when over partitions), GROUP BY with MAX(id)/MIN(id) etc because we wont something other than the first or last item in the sub-sets.

The sequence problem itself can be solved more efficiently in many databases with vendor specific code, but this is vendor specific and not universal; and remember, not all versions of any database support stored procedures and some don't support them at all.

[Roger Ramjet]

Whatever happened to the edit button? Can't even correct my spelling any more

I realise that I did not answer your question fully sneakyimp. The COUNT has to be run of course, but it is the GROUP BY that create the overhead in a query like this and that is quite normal no matter what method one uses.