Subquery Issue
-
Hello. My main issue is with SQL Server 2000 NOT having a direct equivalent to MySQL's LIMIT / OFFSET function. This is the original query:
SELECT w.*, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY w.wid DESC LIMIT 0, 50
And this is the converted query (that doesn't work):
SELECT * FROM
(SELECT TOP 50 * FROM
(SELECT TOP 50 w.*, u.name, u.uid FROM watchdog w INNER JOIN users u on w.uid = u.uid ORDER BY w.wid asc ) as foo
ORDER BY w.wid desc ) as bar
ORDER BY w.wid asc
It fails with the following errors:
Msg 8156, Level 16, State 1, Line 1
The column 'uid' was specified multiple times for 'foo'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "w.wid" could not be bound.
Msg 8156, Level 16, State 1, Line 1
The column 'uid' was specified multiple times for 'bar'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "w.wid" could not be bound.
I am convinced that this is due to the INNER JOIN statement being used, but I'm not sure what is wrong. Any suggestions are welcome including alternative ways to adapt the LIMIT/OFFSET to SQL Server.
-
Change the w.* to w.col1,w.col2....w.coln. The reason for the error is name and id repeating multiple times. You cannot have the same column name repeating when u create a table/view.
Have u tried this:
SELECT TOP 50 w.*
FROM watchdog w INNER JOIN users u on w.uid = u.uid
ORDER BY w.wid desc
-
Thanks for your reply! Trying the query you suggested works. I tried replacing w.* with w.wid in my query, but that threw the following error (the same goes for if I remove selecting w.* entirely):
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "w.wid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "w.wid" could not be bound.
Any ideas?
Basically I am converting all MySQL queries with the LIMIT function to work with SQL Server. The nested TOPs are how I decided to do this. Since I'm in SQL Server 2000, I don't have access to 2005's ROWCOUNT (or something like that, can't remember exactly).
-
Remove the w. in the 2 order by.
-
Also this is what you are looking for :
/*
Ivica Masar
*/
USE pubs
DECLARE @var1 VARCHAR(12)
DECLARE @var2 VARCHAR(30)
SET ROWCOUNT 17
SELECT @var1 = au_id, @var2 = au_lname FROM authors ORDER BY au_lname, au_id
SET ROWCOUNT 0
SELECT TOP 3 * FROM authors
WHERE (au_id >= @var1 AND au_lname=@var2) OR ( au_lname>@var2)
ORDER BY au_lname, au_id
-
Removing the w. in the order by works, but I need to sort by that unfortunately. The ROWCOUNT solution would work, but SQL SERVER 2005 is not an option right now, just 2000. The hard part is that I need a formula for converting MySQL LIMIT/OFFSET queries to SQL SERVER compatible queries... and my formula was working until this particular query using an INNER JOIN came up
Thanks again guys for your input... I really apppreciate it. Any more ideas?
-
My version is compatible with 2000!!!!!!
-
So it is! Let me try adapting my query and see how it works...
-
ROWCOUNT is being deprecated. It still works in 2005, but it probably won't be fully supported in future versions. I wouldn't rely too heavily on it. From BOL for SQL 2005:
Important: Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Good point... but it will work with 2000, it will work for 2k5. Then when they need to upgrade to 2k10, they'll just have to move that variable ot the top clause... not a big trade off for something that works now IMHO.
-
Yeah, I'm definitely trying to stick with core functionality in order to make this last. My issue with the above solution(s) is that my conversion function must be dynamic in that I don't know what the incoming query is (other than it has LIMIT in it) that needs to be converted to MSSQL. So, for example, I WAS using the following to convert LIMITs, but certain incoming queries would break it:
Original Query
SELECT w.*, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY w.wid DESC LIMIT 0, 50
Conversion Function
SELECT * FROM (
SELECT TOP n * FROM (
SELECT TOP (n + offset) columns
FROM tablename
ORDER BY key ASC
) AS FOO ORDER BY key DESC
) AS BAR ORDER BY key ASC
Converted Query
SELECT * FROM
(
SELECT TOP 50 * FROM
(
SELECT TOP 50 w.*, u.name, u.uid
FROM watchdog w
inner join users u on w.uid = u.uid
ORDER BY w.wid asc
)
AS foo
ORDER BY w.wid desc
)
AS bar
ORDER BY w.wid asc
Result
Msg 8156, Level 16, State 1, Line 1
The column 'uid' was specified multiple times for 'foo'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "w.wid" could not be bound.
Msg 8156, Level 16, State 1, Line 1
The column 'uid' was specified multiple times for 'bar'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "w.wid" could not be bound.
The innermost query works on its own perfectly. However, when it is set within the generic conversion, it breaks to the the two ORDER BY statements since uid is a foreign key in the w (watchdog) table. I thought of aliasing the SELECTed u.name and u.uid, which worked, but because the w.* was also being SELECTed, I had no way of aliasing it or really being able to accurately anticipate it.
There MUST be some kind of generic way of making all queries using a LIMIT [offset,] range statement (as in MySQL and others) adapt to SQL Server 2000. SQL Server 2000 being the key here, since 2005 does have a solution. I, unfortunately, am stuck with 2000 for the time being.
What do you all think? I've tried NOT IN statements, and temp tables - but without knowing what the incoming query is, it's hard to set up a temp table with the correct columns.
Quite the puzzle if you ask me...
-
Maybe my only issue is the aliasing part and that if I could determine all the individual columns in "w.*" I could simply alias all of them dynamically, which would most likely solve my issue. Just seems a bit like duct tape.
-
yup.
Important: Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply