0
 SELECT * FROM Customers
 WHERE Country='Germany'
 LIMIT 3;

This is my sql query and i working with northwind database but sql limit is not working is it deprecated ?

Panagiotis Kanavos
  • 104,344
  • 11
  • 159
  • 196
Berk Cinaz
  • 137
  • 3
  • 7
  • Based ion Northwind I guess it is MS SQL Server Could you please try SELECT TOP 3* FROM CUSTOMERS WHERE COUNTRY='GERMANY' – Sergey May 21 '21 at 07:16
  • @Sergey this query could be work but i just wondering – Berk Cinaz May 21 '21 at 07:17
  • 4
    SQL Server doesn't use `LIMIT` - that's MySQL (SQL Server uses `TOP` and/or `OFFSET`). However, `LIMIT` _without_ an `ORDER BY` will give you undefined results. **Always use `ORDER BY` with `LIMIT`!** – Dai May 21 '21 at 07:18
  • 3
    This clause TOP/LIMIT is db-specific,so could you please clarify your RDBMS – Sergey May 21 '21 at 07:19
  • @Sergey Northwind is also MS Access (in 2021? lol...), the current sample database for SQL Server is AdventureWorks. – Dai May 21 '21 at 07:20
  • i'm using microsoft sql server – Berk Cinaz May 21 '21 at 07:21
  • @BerkCinaz What version of SQL Server? – Dai May 21 '21 at 07:21
  • and i learning from this website [https://www.w3schools.com/sql/sql_top.asp] – Berk Cinaz May 21 '21 at 07:21
  • last version @Dai – Berk Cinaz May 21 '21 at 07:22
  • 5
    @BerkCinaz W3Schools.com is a poor resource for learning web-development - most of their content is literally decades old, obsolete, and/or does not encourage modern _safe_ development practices. – Dai May 21 '21 at 07:22
  • 1
    @BerkCinaz What do you mean by "last version"? Tell us the **version number**. – Dai May 21 '21 at 07:23
  • on that site I do read "Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM." – Luuk May 21 '21 at 07:23
  • @Dai what could u prefer ? – Berk Cinaz May 21 '21 at 07:23
  • @BerkCinaz MIT OpenCourseWare, for SQL it's this one: https://ocw.mit.edu/courses/electrical-engineering-and-computer-science/6-830-database-systems-fall-2010/ – Dai May 21 '21 at 07:23
  • @Dai sry i just update yesterday, Sql Server Version = 15.0.18384.0 – Berk Cinaz May 21 '21 at 07:24
  • @Dai Adventureworks is the *old* sample database. Northwind is the ancient, terrible one that shouldn't be used. The current one is WordlWideImporters which targets 2016 and later. Haven't checked it though – Panagiotis Kanavos May 21 '21 at 07:28
  • @BerkCinaz W3CSchools is a resource for web development, not database development. – Panagiotis Kanavos May 21 '21 at 07:30
  • @Luuk that's wrong too. Which makes W3CSchools really terrible for DB development. SQL Server also supports ROWNUM, and so does MySQL since v8. That's *not* equivalent to TOP/LIMIT though, and won't produce the same execution plans. The *standard* way is to use `FETCH FIRST ....` but SQL Server only allows this with `ORDER BY` – Panagiotis Kanavos May 21 '21 at 07:34
  • @BerkCinaz no database offers more than entry-level support for the SQL standard, so you have to know about the differences in every database. Part of reason is that the SQL standardization process is VERY slow. `FETCH FIRST` was added [in SQL:2008](https://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause), although databases supported this one way or another since the 1990s. The Wikipedia page I linked shows just how many different ways are used for the same functionality. When a new feature is standardized it may conflict with existing implementations, leading to even more delays – Panagiotis Kanavos May 21 '21 at 07:38
  • @PanagiotisKanavos: There was, at least, some hint about the usage of `LIMIT`, and [Google](https://www.google.com/search?q=sql+limit+site%3Astackoverflow.com) says [How to implement LIMIT with SQL Server?](https://stackoverflow.com/questions/603724/how-to-implement-limit-with-sql-server) – Luuk May 21 '21 at 07:50
  • 1
    @Luuk the accepted answer is 1000% wrong. This will calculate the ROW_NUMBER for *all* columns before filtering. The real answer is the second one. `LIMIT 10,20` is equivalent to `OFFSET 10 FETCH NEXT 10 ROWS ONLY`. – Panagiotis Kanavos May 21 '21 at 08:18
  • @PanagiotisKanavos: if you really think that that answer is soo wrong, why do you not add a comment to it? – Luuk May 21 '21 at 08:21
  • @Luuk because others have already done so and posted the correct answer – Panagiotis Kanavos May 21 '21 at 08:21
  • @PanagiotisKanavos: This is also 1000% wrong: "This will calculate the ROW_NUMBER for all columns before filtering" (I did a test on MSSQL 2017) – Luuk May 21 '21 at 08:27
  • 1
    @Luuk only if you got identical execution plans with `TOP N`, in which case this would be a recent optimization. When I try this with SQL Server 2019 though, I get a query plan that's 10x more expensive *and* gets statistics wrong. `TOP 10` and `OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY` produce an identical, cheap execution plan – Panagiotis Kanavos May 21 '21 at 08:41
  • @PanagiotisKanavos: the reasons for a full table scan in that example are because of a missing index. – Luuk May 21 '21 at 08:44

0 Answers0