MySQL Forums
Forum List  »  Microsoft SQL Server

Problem with tSQL query on MySQL
Posted by: Abisola Fatokun
Date: January 04, 2005 04:05AM

Hi guys,

I am unable to get this query to work with MySQL even though it works just fine with MS SQL:

SELECT *
FROM (SELECT first_consecutive = LEAST(capcode), last_consecutive, (last_consecutive - LEAST(capcode) + 1) AS dlength
FROM (SELECT P1.capcode, LEAST(P2.capcode) AS last_consecutive
FROM ch1fragmentcapcodes AS P1
LEFT JOIN (SELECT P1.capcode
FROM ch1fragmentcapcodes AS P1
LEFT JOIN ch1fragmentcapcodes AS P2
ON P2.capcode = P1.capcode + 1
WHERE P2.capcode IS NULL) AS P2
ON P2.capcode >= P1.capcode
GROUP BY P1.capcode) AS P
GROUP BY last_consecutive) AS P
WHERE dlength >= 5
ORDER BY first_consecutive

My understanding was that MySQL versions below 4.1 did not support subqueries and I upgraded my version accordingly but still kept getting the ambiguous syntax errors!
What I'm after is to go through a table and pick out sequential (or consecutive) numbers from it.

Would appreciate it if someone could help with this.

Options: ReplyQuote


Subject
Written By
Posted
Problem with tSQL query on MySQL
January 04, 2005 04:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.