April 1, 2011

SQL limit

A lot of you are missing LIMIT of MySql, but with some minor changes you can do this.
So asuming we have a tables called TreeEmployees we will use ROW_NUMBER() sql function as an order index to the where clause.
Let's say we have a simle query
FROM [Sample].[dbo].[TreeEmployees]
but we want to paginate this
, ROW_NUMBER() OVER (ORDER BY Name) as row FROM TreeEmployees ) tree
WHERE row >= 1 and row <= 10
This query will just take the first 10 records. Notice that first row will just have index 1 not 0.

74Anna MendezMember of Joyce's teamWorker1
92Carter BowlsJan's team 1Team Leader2
2NULLJan RightJan folowersChief Account manager3
102Jimmy ClarckJan's team 2Team Leader4
1NULLJohn DoeJohn folowersTechnical Manager5
41Joyce May AnnJoyce teamTeam Leader6
63Laurel BeanMember of Flynn's teamWorker7
1310Lilly Ann ParkerJimmy's teamWorker8
129Louise SennaCarter's teamWorker9
84Maria FernandezMember of Joyce's teamWorker10

kick it on DotNetKicks.com

No comments:

Post a Comment