Trying to order returned results of a SP by date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to order returned results of a SP by date

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-15 : 05:50:34

Hi,

I implemented a system of paging in my ASP.NET VB.NET application so that the user can navigate forward and backward through the returned Database results. To do so, I use a SP inside SQL2000 Server that uses two variables passed in by the app, @CurrentPage and @PageSize, the first one is the number of the current page the user is seeing the results, and the second one is the number of items (rows) are displayed in every page. These variables are used to return, every time the SP is called, only the number of rows allowed per page, avoiding to return in every page 5000 records only to display 10 of them. So, this SP retrieves only the necessary data for every page, and it has a very fast performance.

Now I order the returned results by ‘Offer_id’, and I would like to order by ‘theDate’ (a datetime column). To do so I don’ t know if I need to order also by ‘Offer_id’, but the idea is that I only want to order by theDate, since I want the users can see the Offers inserted in the database by other users from the most recent to the most old Offer.

Here is the SP now:


USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalItems int output
AS
SET NOCOUNT ON

DECLARE @v_Date DATETIME
DECLARE @v_FirstOID bigint
DECLARE @v_LastOID bigint 
DECLARE @v_FirstDate Datetime
DECLARE @v_LastDate Datetime
DECLARE @v_MaxRowNumber INT

SET @v_Date = GETDATE()

SET @v_MaxRowNumber = @CurrentPage * @PageSize 
SET @TotalItems = 0

SELECT @TotalItems = @TotalItems + 1 
,@v_FirstOID = CASE WHEN ((@TotalItems <= @v_MaxRowNumber) AND ((@TotalItems % @PageSize = 1) OR @PageSize = 1)) THEN abt.Offer_id
ELSE @v_FirstOID
END
,@v_FirstDate = CASE WHEN ((@TotalItems <= @v_MaxRowNumber) AND ((@TotalItems % @PageSize = 1) OR @PageSize = 1)) THEN abt.theDate
ELSE @v_FirstDate 
END
,@v_LastOID = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.Offer_id
ELSE @v_LastOID 
END
,@v_LastDate = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.theDate
ELSE  @v_LastDate 
END
FROM
(
  SELECT 
   Offer_id, theDate
  FROM Offers AS ofe
  INNER JOIN TheUsers As us On ofe.User_num= us.User_id
  INNER JOIN Cities As city On ofe.city_num = city.City_id
) AS abt   
  
  ORDER BY abt.Offer_id ASC

    SELECT Offer_id, theDate, Title, us.Company_name, city.City_name
    FROM Offers AS ofe
    INNER JOIN TheUsers As us On ofe.User_num= us.User_id
    INNER JOIN Cities As city On ofe.city_num = city.City_id    
    Where (Offer_id >= @v_FirstOID) AND (Offer_id <= @v_LastOID)
    ORDER BY Offer_id ASC
GO

How can I order the returned results by theDate from the most recent Offer to the most old one?

Thank you,
Cesar

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-15 : 06:13:11

if theDate is a datetime column, then:


order by
	theDate desc

CODO ERGO SUM

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-15 : 06:43:57

No, only doing this change it doesn’ t work. In this way it does not return back any row.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-15 : 09:01:13

if you want to page by Date you need to order both of your SELECT's by date.

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-16 : 06:28:29

I found a way I can order by date, tell me if it is correct, now with my data in the table works:


USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalItems int output
AS
SET NOCOUNT ON

DECLARE @v_FirstOID bigint
DECLARE @v_LastOID bigint 
DECLARE @v_FirstDate datetime
DECLARE @v_LastDate datetime
DECLARE @v_MaxRowNumber int


SET @v_MaxRowNumber = @CurrentPage * @PageSize 
SET @TotalItems = 0

SELECT @TotalItems = @TotalItems + 1 
,@v_FirstOID = CASE WHEN (@TotalItems = @v_MaxRowNumber - (@PageSize - 1)) THEN abt.Offer_id
ELSE @v_FirstOID
END
,@v_FirstDate = CASE WHEN (@TotalItems = @v_MaxRowNumber - (@PageSize - 1)) THEN abt.theDate
ELSE @v_FirstDate 
END
,@v_LastOID = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.Offer_id
ELSE @v_LastOID 
END
,@v_LastDate = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.theDate
ELSE  @v_LastDate 
END
FROM
(
  SELECT 
   Offer_id, theDate
  FROM Offers AS ofe
  INNER JOIN TheUsers As us On ofe.User_num= us.User_id
  INNER JOIN Cities As city On ofe.city_num = city.City_id
) AS abt   
  
  ORDER BY abt.Offer_id Desc, abt.theDate Desc

    SELECT Offer_id, theDate, Title, us.Company_name, city.City_name
    FROM Offers AS ofe
    INNER JOIN TheUsers As us On ofe.User_num= us.User_id
    INNER JOIN Cities As city On ofe.city_num = city.City_id    
    Where (Offer_id <= @v_FirstOID) AND (Offer_id >= @v_LastOID)

    ORDER BY Offer_id Desc, theDate Desc

SET NOCOUNT OFF
GO

The changes are:

Because I am ordering in descendent way I have to change the ‘where’ clause to ‘Where (Offer_id <= @v_FirstOID) AND (Offer_id >= @v_LastOID)’. I mean, the operands ‘less or equal than’ and ‘greater or equal than’.

In the ‘order’ clause I have to put it in this way ‘ORDER BY Offer_id Desc, theDate Desc
’, I mean first order by Offer_id and then by theDate, otherwise it doesn’ t return any records to the page. For example if I put it thus: ORDER BY theDate Desc, Offer_id Desc, it doesn’ t return any records to the page, perhaps it is something related to null values that the theDate column has in some rows..

Is it correct order the results by date in this way? If I do it in this way I always will get the offers well ordered from most recent to most old ones?

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources