Order by and Limit syntax

Question

Following SQL sentence throws an error on ORDER BY + LIMIT syntax so, if I remove LIMIT, works properly. Is there any syntax error or how to add LIMIT?

SELECT * FROM dbo.T0

WHERE id < (
  SELECT ISNULL((SELECT a.id FROM dbo.T0 a, dbo.T0 b, dbo.T0 c 
  WHERE 
    (a.id = b.id + 1 AND a.var1 != b.var1) 
  AND 
    c.id < a.id
  AND 
    c.timeD = '2015/08/10 18:38:00'
  AND
    c.var1 = 2
  ORDER BY a.id LIMIT 1), (SELECT MAX(id) + 1 FROM dbo.T0)))
...

Which dbms are you using? (Many products have their own syntax versions here...) — jarlh, Aug 14 '15 at 09:49
SQL Server has TOP. I'm not sure, but perhaps newer SQL Server versions have FETCH FIRST too, the ANSI SQL way. — jarlh, Aug 14 '15 at 09:55

Raphaël Althaus · Accepted Answer · 2015-08-14T10:17:28.307

There's no LIMIT in sql server.

You can use a Select top 1 *, but in your case I would rather use row_number and Common table expressions (cte).

So (with some join syntax also)

with cte as (select 
             a.id, 
             row_number() over(order by a.Id) rn
             from dbo.T0 a 
             join dbo.T0 b on a.id = b.id + 1 and a.var1 != b.var1
             join dbo.T0 c on c.id < a.id
             where c.timeD = '2015/08/10 18:38:00'
             and c.var1 = 2),
     cte2 as (select id from cte where rn = 1
              union
              SELECT MAX(id) + 1 as id FROM dbo.T0)

select * from dbo.T0 where id < (select min(id) from cte2)
-- we take the min, here, 
--as we want the first part of the union of cte2 if it exists
-- and it will always be smaller then max(id) + 1

Order by and Limit syntax

1 Answers1