Distinct statement not removing duplicate rows in stored procedure

My stored procedure does not work once I add distinct to the select statement. It displays no data available in C# gridview.

This is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[die2]@PageIndex INT = 1,@PageSize INT = 15,@RecordCount INT OUTPUT
AS
BEGINSET NOCOUNT ON;SELECT DISTINCTROW_NUMBER() OVER (ORDER BY d.[id] ASC) AS RowNumber,d.[dieno],d.[templateno],d.[model],d.[type],d.[comment],d.[lmacross], d.[lmaround],j.[pid], j.[description], o.[supplier],s.[no],o.[deliverydate], o.[orderid], o.[waybillno]INTO #ResultsFROM[job_die] d INNER JOIN stock s ON d.id = s.typeid   INNER JOIN orders o ON s.orderid = o.orderid  INNER JOIN job j ON j.diereferneceno = d.id WHEREo.producttype = '4' AND s.active = '1' SELECT @RecordCount = COUNT(*)FROM #ResultsSELECT * FROM #ResultsWHERE RowNumber BETWEEN (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1DROP TABLE #Results
END

Please help

sql-server

modify the query as

AS
BEGIN SET NOCOUNT ON; SELECT DISTINCT d.id, DENSE_RANK() OVER (ORDER BY d.[id] ASC) AS RowNumber,
d.[dieno],d.[templateno],d.[model],d.[type],d.[comment],d.[lmacross], d.[lmaround],j.[pid], j.[description], o.[supplier],s.[no],o.[deliverydate], o.[orderid], o.[waybillno]INTO #ResultsFROM[job_die] d INNER JOIN stock s ON d.id = s.typeid INNER JOIN orders o ON s.orderid = o.orderid INNER JOIN job j ON j.diereferneceno = d.id WHEREo.producttype = '4' AND s.active = '1' SELECT @RecordCount = COUNT(*)FROM #ResultsSELECT * FROM #ResultsWHERE RowNumber BETWEEN (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1DROP TABLE #Results
END

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
modify the query as
AS
BEGIN SET NOCOUNT ON; SELECT DISTINCT d.id, DENSE_RANK() OVER (ORDER BY d.[id] ASC) AS RowNumber,
d.[dieno],d.[templateno],d.[model],d.[type],d.[comment],d.[lmacross], d.[lmaround],j.[pid], j.[description], o.[supplier],s.[no],o.[deliverydate], o.[orderid], o.[waybillno]INTO #ResultsFROM[job_die] d INNER JOIN stock s ON d.id = s.typeid   INNER JOIN orders o ON s.orderid = o.orderid  INNER JOIN job j ON j.diereferneceno = d.id WHEREo.producttype = '4' AND s.active = '1' SELECT @RecordCount = COUNT(*)FROM #ResultsSELECT * FROM #ResultsWHERE RowNumber BETWEEN (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1DROP TABLE #Results
END
+3