SQL Server ROW_NUMBER Left Join + when you don't know column names

Question

I'm writing a page that will create a query (for non-db users) and it create the query and run it returning the results for them.

I am using row_number to handle custom pagination.

How do I do a left join and a row_number in a subquery when I don't know the specific columns I need to return. I tried to use * but I get an error that

The column '' was specified multiple times

Here is the query I tried:

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY Test) AS ROW_NUMBER, *
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50

That did not work. I wonder if SQL Server 2005 is my issue. I'm trying to test the query using Microsoft SQL Server manamgent studio. — htkhtk, Jun 03 '13 at 16:29
You need to write or dynamically generate a query that lists the specific columns and that does not include the same column name twice. — Martin Smith, Jun 03 '13 at 16:56
@MartinSmith - Is there a way to include that query that returns the specific columns names in that sql or would it be a separate query AND I would have to figure out how to include the table name in the column like Table1.fieldone, Table2.fieldtwo — htkhtk, Jun 05 '13 at 20:01

score 0 · Answer 1 · answered May 30 '13 at 17:43

Your query is going to fail in SQL Server regardless of the row_number() call. The * returns all columns, including a.id and b.id. These both have the same name. This is fine for a query, but for a subquery, all columns need distinct names.

You can use row_number() for an arbitrary ordering by using a "subquery with constant" in the order by clause:

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY (select NULL)) AS ROW_NUMBER, *
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50 ;

This removes the dependency on the underlying column name (assuming none are named ROW_NUMBER).

ATR · Answer 2 · 2013-06-03T16:33:29.240

0

Try this sql. It should work.

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY a.Test) AS ROW_NUMBER, a.*,b.*
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50

edited Jun 03 '13 at 16:33

answered Jun 01 '13 at 14:32

ATR

2,160
4
22
43

This doesn't seem to work, I still get the error about the column being specific multiple times. I am using SQL Server 2005. Does that changed anything? – htkhtk Jun 03 '13 at 16:27
@htkhtk : can you please change order by Test to either order by a.Test or order by b.Test. May be that is the problem. – ATR Jun 03 '13 at 16:32

SQL Server ROW_NUMBER Left Join + when you don't know column names

2 Answers2

Linked