tag:blogger.com,1999:blog-3212052278624963532023-11-15T06:43:51.585-08:00.Net Programminglthttp://www.blogger.com/profile/17164667078965864307noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-321205227862496353.post-48434947741035633002011-04-01T10:37:00.000-07:002011-04-01T11:08:41.589-07:00SQL limitA lot of you are missing <strong>LIMIT</strong> of MySql, but with some minor changes you can do this.<br />So asuming we have a tables called <a href="http://code-cs.blogspot.com/2011/03/sql-20052008-recursive-query.html">TreeEmployees</a> we will use <strong>ROW_NUMBER()</strong> sql function as an order index to the where clause.<br />Let's say we have a simle query<br /><pre class="csharpcode"><span class="kwrd">SELECT</span> [Id]<br />,[ParentId]<br />,[Name]<br />,[Description]<br />,[<span class="kwrd">Position</span>]<br /><span class="kwrd">FROM</span> [Sample].[dbo].[TreeEmployees]</pre>but we want to paginate this<br /><pre class="csharpcode"><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> (<br /><span class="kwrd">SELECT</span> [Id]<br />,[ParentId]<br />,[Name]<br />,[Description]<br />,[<span class="kwrd">Position</span>]<br />, ROW_NUMBER() <span class="kwrd">OVER</span> (<span class="kwrd">ORDER</span> <span class="kwrd">BY</span> Name) <span class="kwrd">as</span> <span class="kwrd">row</span> <span class="kwrd">FROM</span> TreeEmployees ) tree<br /><span class="kwrd">WHERE</span> <span class="kwrd">row</span> >= 1 <span class="kwrd">and</span> <span class="kwrd">row</span> <= 10</pre>This query will just take the first 10 records. Notice that first row will just have index 1 not 0.<br /><br /><table><tbody><tr><td>Id</td><td>ParentId</td><td>Name</td><td>Description</td><td>Position</td><td>row</td></tr><tr><td>7</td><td>4</td><td>Anna Mendez</td><td>Member of Joyce's team</td><td>Worker</td><td>1</td></tr><tr><td>9</td><td>2</td><td>Carter Bowls</td><td>Jan's team 1</td><td>Team Leader</td><td>2</td></tr><tr><td>2</td><td>NULL</td><td>Jan Right</td><td>Jan folowers</td><td>Chief Account manager</td><td>3</td></tr><tr><td>10</td><td>2</td><td>Jimmy Clarck</td><td>Jan's team 2</td><td>Team Leader</td><td>4</td></tr><tr><td>1</td><td>NULL</td><td>John Doe</td><td>John folowers</td><td>Technical Manager</td><td>5</td></tr><tr><td>4</td><td>1</td><td>Joyce May Ann</td><td>Joyce team</td><td>Team Leader</td><td>6</td></tr><tr><td>6</td><td>3</td><td>Laurel Bean</td><td>Member of Flynn's team</td><td>Worker</td><td>7</td></tr><tr><td>13</td><td>10</td><td>Lilly Ann Parker</td><td>Jimmy's team</td><td>Worker</td><td>8</td></tr><tr><td>12</td><td>9</td><td>Louise Senna</td><td>Carter's team</td><td>Worker</td><td>9</td></tr><tr><td>8</td><td>4</td><td>Maria Fernandez</td><td>Member of Joyce's team</td><td>Worker</td><td>10</td></tr></tbody></table><br /><a href="http://www.dotnetkicks.com/kick/?url=http%3a%2f%2fcode-cs.blogspot.com%2f2011%2f04%2fsql-limit.html"><img src="http://www.blogger.com/http%3A%2F%2Fwww.dotnetkicks.com%2FServices%2FImages%2FKickItImageGenerator.ashx%3Furl%3Dhttp%253a%252f%252fcode-cs.blogspot.com%252f2011%252f04%252fsql-limit.html%26fgcolor%3D00CCFF" border="0" alt="kick it on DotNetKicks.com" /></a>lthttp://www.blogger.com/profile/17164667078965864307noreply@blogger.com0tag:blogger.com,1999:blog-321205227862496353.post-27345922535858144942011-03-26T02:13:00.000-07:002011-04-01T11:05:20.154-07:00Sql 2005/2008 recursive query<div>Let's have the following example:<br />We have a table with and Id and ParentId hold relations between employees and we want to see all employees that are under a manager or team leader.<br />First the sql table and data<pre class="csharpcode"><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> [dbo].[TreeEmployees](<br /> [Id] [bigint] <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [ParentId] [bigint] <span class="kwrd">NULL</span>,<br /> [Name] [<span class="kwrd">varchar</span>](50) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [Description] [<span class="kwrd">varchar</span>](200) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /> [<span class="kwrd">Position</span>] [<span class="kwrd">varchar</span>](50) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>,<br /><span class="kwrd">CONSTRAINT</span> [PK_Tree] <span class="kwrd">PRIMARY</span> <span class="kwrd">KEY</span> <span class="kwrd">CLUSTERED</span><br />(<br /> [Id] <span class="kwrd">ASC</span><br />)<span class="kwrd">WITH</span> (PAD_INDEX = <span class="kwrd">OFF</span>, STATISTICS_NORECOMPUTE = <span class="kwrd">OFF</span>, IGNORE_DUP_KEY = <span class="kwrd">OFF</span>, ALLOW_ROW_LOCKS = <span class="kwrd">ON</span>, ALLOW_PAGE_LOCKS = <span class="kwrd">ON</span>) <span class="kwrd">ON</span> [<span class="kwrd">PRIMARY</span>]<br />) <span class="kwrd">ON</span> [<span class="kwrd">PRIMARY</span>]<br /><br /><span class="kwrd">GO</span><br /><span class="kwrd">SET</span> ANSI_PADDING <span class="kwrd">OFF</span><br /><span class="kwrd">GO</span><br /><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> [dbo].[TreeEmployees] <span class="kwrd">ADD</span> <span class="kwrd">CONSTRAINT</span> [DF_Tree_Name] <span class="kwrd">DEFAULT</span> (<span class="str">''</span>) <span class="kwrd">FOR</span> [Name]<br /><span class="kwrd">GO</span><br /><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> [dbo].[TreeEmployees] <span class="kwrd">ADD</span> <span class="kwrd">CONSTRAINT</span> [DF_Tree_Description] <span class="kwrd">DEFAULT</span> (<span class="str">''</span>) <span class="kwrd">FOR</span> [Description]<br /><span class="kwrd">GO</span><br /><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> [dbo].[TreeEmployees] <span class="kwrd">ADD</span> <span class="kwrd">CONSTRAINT</span> [DF_Tree_Position] <span class="kwrd">DEFAULT</span> (<span class="str">''</span>) <span class="kwrd">FOR</span> [<span class="kwrd">Position</span>]<br /><span class="kwrd">GO</span><br /><br /><br />Add sql data<br /></pre><pre class="csharpcode"><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (1, <span class="kwrd">NULL</span>, N<span class="str">'John Doe'</span>, N<span class="str">'John folowers'</span>, N<span class="str">'Technical Manager'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (2, <span class="kwrd">NULL</span>, N<span class="str">'Jan Right'</span>, N<span class="str">'Jan folowers'</span>, N<span class="str">'Chief Account manager'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (3, 1, N<span class="str">'Martin Flynn'</span>, N<span class="str">'Flyn team'</span>, N<span class="str">'Team Leader'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (4, 1, N<span class="str">'Joyce May Ann'</span>, N<span class="str">'Joyce team'</span>, N<span class="str">'Team Leader'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (5, 3, N<span class="str">'Mia Donovan'</span>, N<span class="str">'Meber of Fylnn'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (6, 3, N<span class="str">'Laurel Bean'</span>, N<span class="str">'Member of Flynn'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (7, 4, N<span class="str">'Anna Mendez'</span>, N<span class="str">'Member of Joyce'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (8, 4, N<span class="str">'Maria Fernandez'</span>, N<span class="str">'Member of Joyce'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (9, 2, N<span class="str">'Carter Bowls'</span>, N<span class="str">'Jan'</span><span class="str">'s team 1'</span>, N<span class="str">'Team Leader'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (10, 2, N<span class="str">'Jimmy Clarck'</span>, N<span class="str">'Jan'</span><span class="str">'s team 2'</span>, N<span class="str">'Team Leader'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (11, 9, N<span class="str">'Sully Westwood'</span>, N<span class="str">'Carter'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (12, 9, N<span class="str">'Louise Senna'</span>, N<span class="str">'Carter'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (13, 10, N<span class="str">'Lilly Ann Parker'</span>, N<span class="str">'Jimmy'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><span class="kwrd">INSERT</span> [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [<span class="kwrd">Position</span>]) <span class="kwrd">VALUES</span> (14, 10, N<span class="str">'Tina Riggs'</span>, N<span class="str">'Jimmy'</span><span class="str">'s team'</span>, N<span class="str">'Worker'</span>)<br /><br />And here is the recursive query:</pre><pre class="csharpcode"><span class="kwrd">declare</span> @rootId bigint<br />--<span class="kwrd">set</span> this <span class="kwrd">to</span> 1 <span class="kwrd">or</span> 2<br /><span class="kwrd">set</span> @rootId = 2;<br /><span class="kwrd">WITH</span> TeamMembers <span class="kwrd">as</span><br />(<br /><span class="kwrd">select</span> Id, ParentId, Name, [Description],[<span class="kwrd">Position</span>], 0 <span class="kwrd">as</span> [<span class="kwrd">level</span>] <span class="kwrd">from</span> TreeEmployees<br /><span class="kwrd">where</span> Id=@rootId<br /><span class="kwrd">union</span> <span class="kwrd">all</span><br /><span class="kwrd">select</span> tc.id, tc.ParentId, tc.Name, tc.[Description],tc.[<span class="kwrd">Position</span>],[<span class="kwrd">level</span>] +1 <span class="kwrd">as</span> [<span class="kwrd">level</span>] <span class="kwrd">from</span> TreeEmployees <span class="kwrd">as</span> tc<br /><span class="kwrd">inner</span> <span class="kwrd">join</span> TeamMembers <span class="kwrd">on</span> tc.ParentId = TeamMembers.Id<br />)<br /><span class="kwrd">select</span> * <span class="kwrd">from</span> TeamMembers</pre><p> </p><p>And here are the results:</p><table><tbody><tr><td>Id</td><td>ParentId</td><td>Name</td><td>Description</td><td>Position</td><td>level</td></tr><tr><td>2</td><td>NULL</td><td>Jan Right</td><td>Jan folowers</td><td>Chief Account manager</td><td>0</td></tr><tr><td>9</td><td>2</td><td>Carter Bowls</td><td>Jan's team 1</td><td>Team Leader</td><td>1</td></tr><tr><td>10</td><td>2</td><td>Jimmy Clarck</td><td>Jan's team 2</td><td>Team Leader</td><td>1</td></tr><tr><td>13</td><td>10</td><td>Lilly Ann Parker</td><td>Jimmy's team</td><td>Worker</td><td>2</td></tr><tr><td>14</td><td>10</td><td>Tina Riggs</td><td>Jimmy's team</td><td>Worker</td><td>2</td></tr><tr><td>11</td><td>9</td><td>Sully Westwood</td><td>Carter's team</td><td>Worker</td><td>2</td></tr><tr><td>12</td><td>9</td><td>Louise Senna</td><td>Carter's team</td><td>Worker</td><td>2</td></tr></tbody></table><br /><br /></div><a href="http://www.dotnetkicks.com/kick/?url=http%3a%2f%2fcode-cs.blogspot.com%2f2011%2f03%2fsql-20052008-recursive-query.html"><img src="http://www.blogger.com/http%3A%2F%2Fwww.dotnetkicks.com%2FServices%2FImages%2FKickItImageGenerator.ashx%3Furl%3Dhttp%253a%252f%252fcode-cs.blogspot.com%252f2011%252f03%252fsql-20052008-recursive-query.html" border="0" alt="kick it on DotNetKicks.com" /></a>lthttp://www.blogger.com/profile/17164667078965864307noreply@blogger.com0