Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I page rsults in SQL Express with a JOIN in the WHERE clause

Posted on 2008-10-09
8
Medium Priority
?
293 Views
Last Modified: 2012-05-05
I have a stored procedure below that returns a bunch of results.  This stored procedure works exactly as I want it to.  Except now I want to do paging within the stored procedure.

I have looked at various samples throughout the Net but don't quite get it.  None of the examples I have seen have any form of SELECT statement with anything outside the ordinary.  They also all seem to order by the IDENTITY column of the selected table or some OVER statement that allows the ORDER BY clause to use a column in the selected table.  I want to order by a COUNT through a joined table.

It is entirely possible the count may be "0" for many of these items as well (no restaurants added as favourites yet and in the future obviously not all restaurants will be added as favourites).  A variation excluding the 0 counted restaurants would be nice but most importantly I need a pageable version with or without the 0 counted restaurants.

I have basically no knowledge of paging so treat me as a complete novice in this.  Also consider the passed variables as PageSize and PageNumber (1 based not 0 based) not StartRow and EndRow as I have seen in some examples.
SELECT Restaurant.* FROM Restaurant
	LEFT JOIN (SELECT RestaurantId, COUNT(*) AS RecCount FROM UserFavourites GROUP BY RestaurantID ) As C 
		ON Restaurant.RestaurantId=C.RestaurantId
	WHERE Restaurant.Active = 1 
	ORDER BY C.RecCount DESC

Open in new window

0
Comment
Question by:dij8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Expert Comment

by:Zippit
ID: 22683640
In MySQL it's pretty straightforward.  You can use the Limit X,Y statement to do so where X is the record number to start returning and Y is the number of records to return.  So the following would return 10 records starting at the 25th record:

SELECT *
FROM tbl_myTable
LIMIT 25, 10

Unfortunately MS SQL doesn't have anything quite so easy.  With MS SQL you are going to have to use some kind of "key" field (typically you want it to be a unique column) and the "top" command.  For example...take the following:

SELECT TOP 10 int_myKey, str_name, dtm_dob
FROM tbl_users
WHERE int_myKey > 100
ORDER BY int_myKey ASC

This query will return the 10 records, starting at key 101.  Obviously there are catches here, like if you don't want to sort on the key (if you are confident the names are unique, you can sort on Name and use it in your Where clause).

But that's it.  Other then writing your own paging logic in a storedproc or in the app making use of the data (if your data is in a dataset or a recordset or even XML you could do this fairly easily, but this requires you keep all data in memory in the app.  Not an ideal design).

HTH
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22683646
The way we do this is by inserting the results into a temporary table that has an identity column and then return the group of rows requested based on the page size and start index.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22683682
I'm working up some sample data to show you how it works, but this should do it.
create procedure up_Restaurant_Get @PageNumber int, @PageSize int
as
set nocount on
;with Restaurants as (
SELECT Restaurant.*, row_number() over(order by by RecCount desc) rn
FROM Restaurant
LEFT JOIN (SELECT RestaurantId, COUNT(*) AS RecCount FROM UserFavourites GROUP BY RestaurantID ) As C 
                ON Restaurant.RestaurantId=C.RestaurantId
        WHERE Restaurant.Active = 1 
)
select * from Restaurants
where (rn-1)/@PageSize = @PageNum

Open in new window

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22683761
I was a little off... this one is right though.
set nocount on
go
create table #Restaurant
 (RestaurantID      int
 ,RestaurantName    varchar(20)
 ,Active            bit
 )
go
create table #UserFavorites 
 (restaurantID      int
 ,userid            int
 )
 
go
insert into #Restaurant values(1,'McDonalds',1)
insert into #Restaurant values(2,'Burger King',1)
insert into #Restaurant values(3,'Wendy''s',1)
insert into #Restaurant values(4,'Hardees',1)
insert into #Restaurant values(5,'Arby''s',1)
insert into #Restaurant values(6,'Dairy Queen',2)
insert into #Restaurant values(7,'Red Lobster',1)
insert into #Restaurant values(8,'Olive Garden',1)
insert into #Restaurant values(8,'Pizza Hut',1)
insert into #Restaurant values(8,'Dominos',1)
go
insert into #userFavorites values(1,1)
insert into #userFavorites values(1,2)
insert into #userFavorites values(1,3)
insert into #userFavorites values(1,4)
 
insert into #userFavorites values(2,1)
insert into #userFavorites values(2,2)
insert into #userFavorites values(2,4)
insert into #userFavorites values(2,1)
 
insert into #userFavorites values(3,1)
insert into #userFavorites values(3,4)
 
insert into #userFavorites values(4,7)
insert into #userFavorites values(4,5)
insert into #userFavorites values(4,2)
insert into #userFavorites values(4,1)
insert into #userFavorites values(4,3)
 
insert into #userFavorites values(5,1)
insert into #userFavorites values(5,2)
insert into #userFavorites values(5,3)
insert into #userFavorites values(5,4)
 
insert into #userFavorites values(6,1)
 
insert into #userFavorites values(7,1)
 
 
go
drop procedure up_Restaurant_Get 
go
create procedure up_Restaurant_Get @PageNumber int, @PageSize int
as
set nocount on
;with Restaurants as (
SELECT r.*,isnull(c.reccount,0) as reccount, row_number() over(order  by RecCount desc) rn
FROM #Restaurant r
LEFT JOIN (SELECT RestaurantId, COUNT(*) AS RecCount FROM #UserFavorites GROUP BY RestaurantID ) As C 
                ON r.RestaurantId=C.RestaurantId
        WHERE r.Active = 1 
)
select ((rn-1)/@PageSize)+1,* from Restaurants
where ((rn-1)/@PageSize)+1 = @PageNumber
 
go
exec up_Restaurant_Get 1,8
exec up_Restaurant_Get 1,2
exec up_Restaurant_Get 2,2
exec up_Restaurant_Get 3,3
 
go
drop table #Restaurant
drop table #UserFavorites 

Open in new window

0
 
LVL 10

Author Comment

by:dij8
ID: 22684204
Fantastic result. :-)

Including the creation and inserting of tables well and truly shows how this all works.  A top notch answer above and beyond.

Once I realised I had to remove the # from the two tables that already exist for me and return the UserFavourites to non-American spelling it worked a treat.
0
 
LVL 10

Author Comment

by:dij8
ID: 22684214
Another quick question, is it possible to select all the columns that match the columns in the Restaurant table and NOT select the "reccount" and "rn" columns without listing each column name.

e.g.
select *, NOT reccount, NOT rn from Restaurants
where ((rn-1)/@PageSize)+1 = @PageNumber

I'm expecting this to not be possible so an answer of "no" is, while sad, sufficient.  After all, there's no bonus points.
0
 
LVL 10

Author Comment

by:dij8
ID: 22684233
Forget it, I already worked it out.  Let me know if it is going to cause any issues though.
ALTER PROCEDURE [dbo].[GetMostPopularRestaurants]
	@PageNumber int,
	@PageSize int
AS
SET NOCOUNT ON
;WITH Restaurants AS (
	SELECT r.*,isnull(c.reccount,0) AS reccount, row_number() OVER(ORDER  BY RecCount DESC) rn
	FROM Restaurant r
	LEFT JOIN (SELECT RestaurantId, COUNT(*) AS RecCount FROM UserFavourites GROUP BY RestaurantID ) AS C 
		ON r.RestaurantId=C.RestaurantId
	WHERE r.Active = 1 
)
SELECT Restaurant.* FROM Restaurant
	INNER JOIN Restaurants r ON r.RestaurantId = Restaurant.RestaurantId
WHERE ((r.rn-1)/@PageSize)+1 = @PageNumber

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22684581
There is no "select all but columna" syntax in SQL server.  

But as you have found, you don't have to select from both the table and the CTE, just the table.
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question