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

dagon;10933008 wrote:
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

Roger Ramjet;10933064 wrote:
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

dagon;10933089 wrote:
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.