April 28, 2008 at 6:32 am
Hi,
My application runs this query using a stored proc
SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3
Now I want to implement paging for the same using Row_Number so that I can display the results in pages.
I tried playing with Row_Number but no luck with it.Basically I am not good with SQL and I had programatically implemented paging in asp.net by looping through all records returned by the query. I want to write the query in a stored proc and return the paged results.
Can someone please help me write a query for the same.
Thanks,
Ganesh
April 28, 2008 at 6:41 am
Ganesh
Please supply table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, and the result set you expect to see. If you show us what you've tried so far with ROW_NUMBER then that'll give us a place to start.
John
April 28, 2008 at 6:55 am
create table emp (empid1 varchar(10),empid2 varchar(10),name1 varchar(10),name2 varchar(10),joindate datetime);
insert into emp values(1,2,'Employee1','Employee2',getdate());
insert into emp values(1,3,'Employee1','Employee3',getdate());
insert into emp values(1,4,'Employee1','Employee4',getdate());
insert into emp values(2,1,'Employee2','Employee1',getdate());
insert into emp values(2,3,'Employee2','Employee3',getdate());
insert into emp values(3,1,'Employee3','Employee1',getdate());
insert into emp values(3,2,'Employee3','Employee1',getdate());
April 28, 2008 at 7:00 am
OK, now please show us what you expect the query to return, and what you've tried so far.
John
April 28, 2008 at 7:10 am
I dont have the queries that I typed saved....i have already posted the query that i am currently using to retrieve
the data .. i want to use paging for the same query by using row_number....
April 28, 2008 at 7:11 am
Perhaps something like this?
SELECT * , ROW_NUMBER ...
FROM
(SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3) sub
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2008 at 7:15 am
I have tried that..it does not work
SELECT * , ROW_NUMBER ... as RowNumber
FROM
(SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3) sub
where RowNumber between 2 and 4
April 28, 2008 at 7:22 am
OK, well you will have to replace the "..." in the snippet Gail gave to get you started with some actual code. Don't forget ROW_NUMBER always has a "()" after it. You can check out the full syntax in Books Online, where you will also find some examples that might look something like what you're trying to do. You said that it's not working - does that mean it gives an error message, or it doesn't return what you expect it to?
John
April 28, 2008 at 7:34 am
SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3
Could be replaced with:
;with
CTE1 (EmpID, Name, JoinDate) as
(selectempid1,name1,joindate
from emp
where empid2=3
union
select empid2,name2,joindate
from emp
where id1=3),
CTE2 (EmpID, Name, JoinDate, Row) as
(select EmpID, Name, JoinDate, row_number() over (order by EmpID)
from CTE1)
select *
from CTE2
where Row between @Start and @End
That will do what you need.
Another way to do it would be to replace the first CTE with a temp table. Depending on the number of rows being returned by your Where and Union, that might work considerably better. If it's more than 200 rows, definitely go with the temp table. If it's between 100 and 200, test both ways (CTE and temp table). If it's a small number of rows now, but is likely to grow into a larger number, the temp table will probably be better in the long run.
create table #Temp (
EmpID int primary key,
Name varchar(100),
JoinDate datetime)
insert into #temp (row, empid, name, joindate)
SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3
;with CTE (EmpID, Name, JoinDate, Row) as
(select empid, name, joindate, row_number() over (order by empid)
from #temp)
select EmpID, Name, JoinDate
from CTE
where row between @start and @end
Of course, the problem with both of these is that each time you query it, even just to increment the row numbers you want to look at, you can end up with different data. Since it's not a single transaction, multiple queries can be "dirty".
If you want to avoid that, either have the front end do the paging, or create a "permanent temp table" that includes some sort of connection ID. When the query is first run, insert data into that table, then keep it there. Perhaps with a user ID as one of the columns. Then, each time the row numbers are incremented (up or down), query that data. That way, it won't change between clicking "next" and clicking "back" on the front-end page.
That solution requires, of course, that you clean up that table in some way. I'm not sure how to manage that in your situation, but perhaps if a few minutes go by without any activity, you could dump the data. There are various solutions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 7:46 am
ganesh.salpure (4/28/2008)
I have tried that..it does not workSELECT * , ROW_NUMBER ... as RowNumber
...
where RowNumber between 2 and 4
Well, obviously not as written. That's just the framework of the query. I left out all the details of the row_number for you to fill in yourself....
It was just to show how a rownumber can be used over a union query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2008 at 9:12 am
Also about the solution...
"
If you want to avoid that, either have the front end do the paging, or create a "permanent temp table" that includes some sort of connection ID. When the query is first run, insert data into that table, then keep it there. Perhaps with a user ID as one of the columns. Then, each time the row numbers are incremented (up or down), query that data. That way, it won't change between clicking "next" and clicking "back" on the front-end page."
The application will be used by hundreds of users. So, creating so many temp tables is not
feasible.
April 28, 2008 at 9:20 am
ganesh.salpure (4/28/2008)
I know you left out the details for me....your query works...but NOT when I want to filter out the rows . When I apply the where clause (where Rownumber>5....) , the query fails with an error "Invalid column name 'rownumber'." I have tried that..it does not work. Unless I filter the rows, the paging is incomplete.Also, the solutions posted above using temp tables CTE1 and CTE2 does not work. error: "'CTE2' has more columns than were specified in the column list."
hmm...
I just copied your test table and GSquared's code, and it ran (once I change ID1 to empid1). perhaps double check that you didn't add somethig else (in the "explicit" syntax for CTE's, each column name must be specified, so having 4 columns defined and 5 in the select would give you that error....)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2008 at 9:28 am
ganesh.salpure (4/28/2008)
Also about the solution..."
If you want to avoid that, either have the front end do the paging, or create a "permanent temp table" that includes some sort of connection ID. When the query is first run, insert data into that table, then keep it there. Perhaps with a user ID as one of the columns. Then, each time the row numbers are incremented (up or down), query that data. That way, it won't change between clicking "next" and clicking "back" on the front-end page."
The application will be used by hundreds of users. So, creating so many temp tables is not
feasible.
Two things:
The idea of a "permanet temp table" doesn't require "so many temp tables", it requires one. Then you have a column in it for something like session ID or connection ID, and a column in it for User ID. You use those to select the data, then the row number (stored in the table) to select the range.
And
If you use any other solution, in which you run a query with "where row between x and y", without persisting the data between calls to the query, you will run the risk of data changing between calls.
For example:
User A selects rows 1-20
User B inserts a new row, which becomes row 19
User A selects rows 21-40
User A goes back to 1-20
When User A goes to 21-40, he'll miss a row, because what used to be row 21, is now row 22. When he goes back to 1-20, he'll have different rows than the first time.
That's with two users. Hundreds of concurrent users? Better make sure everyone understands that "going to the next page may or may not cause you to miss data".
Even if that's acceptable, any use of Row_Number() requires that the whole data set be built by the database, then numbered, then filtered. If you have a few dozen rows, that's no big deal. If you have a few thousand (or, worse, a few million), you're going to have a HUGE performance problem.
User A selects 1-20
The server selects all 10,000 rows, numbers them, picks the first 20
User B selects 61-80
The server selects all 10,000 rows, numbers them, picks 61-80
Users C-ZZZ (78 users in all)
Wait for the server to finish A and B, and then C, and then D, and so on
You either need to pre-populate the data into the primary table, which means you don't have Where clauses other than your "page range", or you load the data once per user per session (which will be slow for that user for their first page), and then use that data for the paging.
This has the problem of possibly creating concurrency issues in updates. You have to judge the thing and ballance it. It will matter, and it's going to be worth taking a little time to look at multiple possible solutions and their impacts on your particular user needs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 9:41 am
Yes..I used the full syntax..the problem is that using where clause to Gail's query throws an error..
But no worries...all of our efforts didnt go waste... I tried this query and it worked.
select * from
(
SELECT * , ROW_NUMBER ... as RowNumber
FROM
(SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3) sub1
)sub2
where RowNumber>5 and RowNumber<10
Thanks Gail Shaw and GSquared for your time and help. Maybe we can focus on query optimization here now.
April 28, 2008 at 11:13 am
Yes, the column has to be created before it can be used in a Where clause, in the case of Row_Number, Rank, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply