How to fetch data within range like "limit" in Mysql using top in Mssql

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%'

Which version of MSSQL? – Joachim Isaksson Oct 06 '14 at 06:39 — Joachim Isaksson, Oct 06 '14 at 06:39
@Ron I posted answer can you try that – Ganesh_Devlekar Oct 06 '14 at 07:23 — Ganesh_Devlekar, Oct 06 '14 at 07:23

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

Try using Top or RowNumber like this example here

Take a look at this How to implement LIMIT with Microsoft SQL Server?

edited May 23 '17 at 12:27

Community

1
1

answered Oct 06 '14 at 07:02

stackMonk

917
17
31

Ok let me try this but I have tried the RowNumber() previously but as my query has distinct and outer joins with multiple table it was difficult to implement this. If possible for you could you please help me to modify my query. – Ron Oct 06 '14 at 07:06
try using TOP,I think that will help you wrap this code without altering it. Anyway I am not a pro in MSSql – stackMonk Oct 06 '14 at 07:29

score 0 · Answer 2 · answered Oct 06 '14 at 07:23

Try like this: For SQL SERVER 2008

select * from 
(
select ROW_NUMBER()  OVER(ORDER BY D.Item_Number) as ROWNUMBER,D.* from 
(
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%'

)D

)M
where M.ROWNUMBER between 1 and 5

score 0 · Accepted Answer · answered Oct 06 '14 at 08:13

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.

How to fetch data within range like "limit" in Mysql using top in Mssql

3 Answers3