SQL limit

2011-04-01T10:37:00.000-07:00

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

SELECT [Id]
,[ParentId]
,[Name]
,[Description]
,[Position]
FROM [Sample].[dbo].[TreeEmployees]

but we want to paginate this

SELECT * FROM (
SELECT [Id]
,[ParentId]
,[Name]
,[Description]
,[Position]
, 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.

Id	ParentId	Name	Description	Position	row
7	4	Anna Mendez	Member of Joyce's team	Worker	1
9	2	Carter Bowls	Jan's team 1	Team Leader	2
2	NULL	Jan Right	Jan folowers	Chief Account manager	3
10	2	Jimmy Clarck	Jan's team 2	Team Leader	4
1	NULL	John Doe	John folowers	Technical Manager	5
4	1	Joyce May Ann	Joyce team	Team Leader	6
6	3	Laurel Bean	Member of Flynn's team	Worker	7
13	10	Lilly Ann Parker	Jimmy's team	Worker	8
12	9	Louise Senna	Carter's team	Worker	9
8	4	Maria Fernandez	Member of Joyce's team	Worker	10

Sql 2005/2008 recursive query

2011-03-26T02:13:00.000-07:00

Let's have the following example:
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.
First the sql table and data

CREATE TABLE [dbo].[TreeEmployees](
  [Id] [bigint] NOT NULL,
  [ParentId] [bigint] NULL,
  [Name] [varchar](50) NOT NULL,
  [Description] [varchar](200) NOT NULL,
  [Position] [varchar](50) NOT NULL,
CONSTRAINT [PK_Tree] PRIMARY KEY CLUSTERED
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TreeEmployees] ADD  CONSTRAINT [DF_Tree_Name]  DEFAULT ('') FOR [Name]
GO
ALTER TABLE [dbo].[TreeEmployees] ADD  CONSTRAINT [DF_Tree_Description]  DEFAULT ('') FOR [Description]
GO
ALTER TABLE [dbo].[TreeEmployees] ADD  CONSTRAINT [DF_Tree_Position]  DEFAULT ('') FOR [Position]
GO


Add sql data

INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (1, NULL, N'John Doe', N'John folowers', N'Technical Manager')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (2, NULL, N'Jan Right', N'Jan folowers', N'Chief Account manager')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (3, 1, N'Martin Flynn', N'Flyn team', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (4, 1, N'Joyce May Ann', N'Joyce team', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (5, 3, N'Mia Donovan', N'Meber of Fylnn''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (6, 3, N'Laurel Bean', N'Member of Flynn''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (7, 4, N'Anna Mendez', N'Member of Joyce''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (8, 4, N'Maria Fernandez', N'Member of Joyce''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (9, 2, N'Carter Bowls', N'Jan''s team 1', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (10, 2, N'Jimmy Clarck', N'Jan''s team 2', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (11, 9, N'Sully Westwood', N'Carter''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (12, 9, N'Louise Senna', N'Carter''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (13, 10, N'Lilly Ann Parker', N'Jimmy''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (14, 10, N'Tina Riggs', N'Jimmy''s team', N'Worker')

And here is the recursive query:

declare @rootId bigint
--set this to 1 or 2
set @rootId = 2;
WITH  TeamMembers  as
(
select Id, ParentId, Name, [Description],[Position], 0 as [level] from TreeEmployees
where Id=@rootId
union all
select tc.id, tc.ParentId, tc.Name, tc.[Description],tc.[Position],[level] +1 as [level] from TreeEmployees as tc
inner join TeamMembers on tc.ParentId = TeamMembers.Id
)
select * from TeamMembers

And here are the results:

Id	ParentId	Name	Description	Position	level
2	NULL	Jan Right	Jan folowers	Chief Account manager	0
9	2	Carter Bowls	Jan's team 1	Team Leader	1
10	2	Jimmy Clarck	Jan's team 2	Team Leader	1
13	10	Lilly Ann Parker	Jimmy's team	Worker	2
14	10	Tina Riggs	Jimmy's team	Worker	2
11	9	Sully Westwood	Carter's team	Worker	2
12	9	Louise Senna	Carter's team	Worker	2

.Net Programming

SQL limit

Sql 2005/2008 recursive query