Yes it appears to be illegal. I am using a JDBC driver for Microsoft SQL Server and it looks like it requires the ONLY term according to the syntax docs https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15 EDIT: Actually in my case, FETCH ? ONLY isn't enough 😞 I would need something like FETCH NEXT 20 ROWS ONLY with terms like NEXT and ROWS like described in this old issue https://github.com/seancorfield/honeysql/issues/58 So it looks like I will need to create a custom clause.

Darn, I thought bits were optional. Sigh. I suspect OFFSET also needs ROW or ROWS for SQL Server? I'll have to do that based on dialect because MySQL can't have ROWS there. PostgreSQL can have OFFSET n or OFFSET n ROWS (of course it can!). And it can either have LIMIT n (like MySQL) or FETCH FIRST n ROWS ONLY (FIRST/NEXT are synonyms, ROW/ROWS are synonyms).

Yep seems like that is the case for OFFSET in SQL Server. What an interesting standard we have for SQL haha

Are you specifying :sqlserver as your dialect, BTW?

I'm going to set it up so if there's :offset and :fetch it will use this verbose style, if there's :offset and :limit it will use the abbreviated style, and if there's :offset on its own, it will only use the verbose style for the :sqlserver dialect -- which I think is the only way to not introduce breaking behavior.

No i am not currently using the dialect option

I think that will work for my use case. The query I have uses both :offset and :fetch

@daniel_hugh OK, 2.0.0-rc5 is available on Clojars. Can you try it out and let me know if it works for you?

Once I get the all-clear from you, I'll go ahead and actually announce that RC 🙂

Sure thing. Let me get on a computer

Great! That works for me 🙂 Thanks for implementing this so quickly 😄

Thank you for catching this before I got to "gold"!

Oh yeah, I suppose the original example that started this all would need to be updated as well with the verbose style. https://github.com/seancorfield/honeysql/blob/develop/doc/clause-reference.md#limit-offset-fetch

(sql/format {:select [:id :name]
             :from [:table]
             :offset 20 :fetch 10})

["SELECT id, name FROM table OFFSET ? ROWS FETCH NEXT ? ROWS ONLY" 20 10]

Yeah, I'll be polishing the docs a lot over the next two weeks. I'll get that updated this evening (although it won't make the cljdoc version until the "gold" release).

OK, that section is updated. Please take a look and let me know if there are any improvements you can think of...

Looks good. I don't have any suggestions at the moment, but will let you know if anything comes up 🙂

thanks! 🙂

By the way, I am not not sure if you caught the second issue in my original post about the fetch helper.

clj꞉user꞉> 
(require '[honey.sql.helpers :as h])
nil
clj꞉user꞉> 
(h/fetch 10)
{:offset 10}
clj꞉user꞉> 

I did not see that, no, sorry.

OK, that's fixed on develop.