如何使用Mssql中的top在Mysql中获取范围内的数据，如“limit”

Question

I have this query which will fetch data from multiple tables and the problem is that it was given by my client which i could not alter too much . What i need is that I want to put limit range like (limit 0,5) in this query but it seems limit is not working in Msssql. So is there any way I could put limit range in this to fetch data .

 select  distinct 
            item.[No_] as Item_Number
            ,item.[Description 3] as Title
            ,item.[ISBN]
            ,item.[Edition] as Edition
            ,item.[Copyright Year] as Copyright_year
            ,item.[Unit Price] as Price
            ,item.[Description] as Author_short
            ,ID.[Notes] as Page_count
            ,M.[Description] as Media_code
            ,item.[Height] as Height
            ,item.[Length] as "Length"
            ,item.[Width] as Width
            ,item.[Net Weight] as "Weight"
            ,item.[Tangible] as Is_Tangible
        from
            "Westmark-Production".dbo.GRT_Prod$Item as item
            join "Westmark-Production".dbo.GRT_Prod$Job as job on item.[No_] = job.[Job Project No_]
            left outer join "Westmark-Production".dbo."GRT_Prod$Item Interior Details" ID ON (ID.[Item No_] = item.[No_] and ID.[Interior Code] = 'FINISHED PAGE COUNT')
            left outer join "Westmark-Production".dbo."GRT_Prod$Item Media Code" M ON M.[Item No_] = item.[No_]
            left outer join "Westmark-Production".dbo."GRT_Prod$Item Ledger Entry" ILE ON ILE.[Item No_] = item.[No_] AND ILE.[Location Code] = 'D01'
        where 1=1
            and item.[Item Disc_ Group] <> '19'
            and item.[Global Dimension 1 Code] <> 'OTHER'
            and len(item.[ISBN]) > 1
            and len(item.[Booklink]) > 1
            and len(item.[No_]) = 6
            and ((job.[Status] <> '3' and item.[Blocked] <> 1) or item.[BlockedReason] NOT IN (1,2,4,5))
            and item.[Tangible] = 1 
            and item.[Product Group Code] not in ('CP','CP KP','KP CP')
            and item.[Prep Type] <> 3
            and item.[Description 3] NOT LIKE '%- ECOMMERCE%'

        union
        select distinct 
            item.[No_] as Item_Number
            ,item.[Description 3] as Title
            ,item.[ISBN]
            ,item.[Edition] as Edition
            ,item.[Copyright Year] as Copyright_year
            ,item.[Unit Price] as Price
            ,item.[Description] as Author_short
            ,ID.[Notes] as Page_count
            ,M.[Description] as Media_code
            ,item.[Height] as Height
            ,item.[Length] as "Length"
            ,item.[Width] as Width
            ,item.[Net Weight] as "Weight"
            ,item.[Tangible] as Is_Tangible
        from
            "Westmark-Production".dbo.GRT_Prod$Item as item
            left outer join "Westmark-Production".dbo."GRT_Prod$Item Interior Details" ID ON (ID.[Item No_] = item.[No_] and ID.[Interior Code] = 'FINISHED PAGE COUNT')
            left outer join "Westmark-Production".dbo."GRT_Prod$Item Media Code" M ON M.[Item No_] = item.[No_]
            left outer join "Westmark-Production".dbo."GRT_Prod$Item Ledger Entry" ILE ON ILE.[Item No_] = item.[No_] AND ILE.[Location Code] = 'D01'
        where 1=1
            and len(item.[Booklink]) > 1
            and len(item.[No_]) = 6
            and item.[Description 3] NOT LIKE '%- ECOMMERCE%'

Answer 1

For 2008 there are a number of things you can do.

If you always want just the X first and have the offset of 0, then there are a number of shotcuts that can be taken to make it easily. You can either use SET ROWCOUNT before your UNION:

SET ROWCOUNT 5
<your union>

Alternatively - you can use TOP over the entirety of your union by wrapping in a Common Table Expression:

;WITH CTE AS (
<your union>
)
SELECT TOP 5 
FROM CTE.

Alternative if you are going to use it for paging, meaning you want an offset larger than 0, then you should start looking into ROW_Number() as the other answers also show.

;WITH CTE AS (
<your union>
), CTE2 AS (
  SELECT *, ROW_Number() OVER (ORDER BY Item_Number) AS RN
)
SELECT * FROM CTE2 
WHERE RN BETWEEN 0 AND 5

(many versions of the syntax exists, this is just one to show).

You could also use the TOP combined with a NOT IN, but ROW_Number() is much easier and a much more versatile tool.