March 9, 2011 at 8:41 pm
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
create table #Data([ID] [Int],[Sub][Int], [Data][Int])
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (1,1,102)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (2,1,65)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (3,1,85)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (4,1,73)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (5,2,12)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (6,2,234)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (7,2,25)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (8,2,2476)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (9,3,45)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (10,3,5)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (11,3,15)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (12,3,54)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (13,4,512)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (14,4,24)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (15,4,24)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (16,4,2)
CREATE CLUSTERED INDEX ip_tID ON #Data(ID);
SELECT * FROM #Data
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
Say my selection is WHERE ID = 3
Then my prev ID would be 2 and my next ID would be 4
and
Say my selection is WHERE ID = 1
Then my prev ID would be <nothing> and my next ID would be 2
and
Say my selection is WHERE ID = 5
Then my prev ID would be 4 and my next ID would be 6
How do I get two recordsets of the NEXT and PREV either in two separate select statments.
Any ideas.:-)
March 10, 2011 at 5:43 am
I'm not sure I follow what you are trying to achieve, but this might be what you are looking for, however it will likely be expensive to run:
select id, sub, data,
(select max(id) from #data where ID < D.ID) as PrevID,
(select min(id) from #data where ID > D.ID) as NextID
from #Data D
--modified per Jim's comment on the typo---Thanks Jim!
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 10, 2011 at 7:32 am
Chazman - nice work. The min select should be aliased as NextID instead. Minor typo.
I agree with Chazman - although this will work, it is not a nice, cheap way of getting records.
Are you trying to loop from within SQL server (cursor, while loop)? If so, is that really necessary. Think of your data in SETs rather than rows. Jeff Moden calls this RBAR (Row By Agonizing Row). The power of SQL server over older technologies is that SQL works with data as Sets. It is slowest if we disregard that and loop.
If you are looping outside of SQL (C#, VB.NET) then the code is quite different of course.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 10, 2011 at 8:10 am
[Jim].[dba].[Murphy] (3/10/2011)
Chazman - nice work. The min select should be aliased as NextID instead. Minor typo.I agree with Chazman - although this will work, it is not a nice, cheap way of getting records.
Are you trying to loop from within SQL server (cursor, while loop)? If so, is that really necessary. Think of your data in SETs rather than rows. Jeff Moden calls this RBAR (Row By Agonizing Row). The power of SQL server over older technologies is that SQL works with data as Sets. It is slowest if we disregard that and loop.
If you are looping outside of SQL (C#, VB.NET) then the code is quite different of course.
Jim
Now-a-days, the MIN/MAX method will work quite well here depending, of course, on effective indexing. Correlated subqueries are usually well absorbed into the execution plan and aren't the RBAR that a lot of folks think they are provided that a "Triangular Join" isn't formed (like some folks do to form a running total, for example). It's like having a formula in a CROSS APPLY. It's treated as if it were a "view".
Make no doubt about, though... correlated subqueries USED to be a form of RBAR but MS has fixed that little problem over time.
--Jeff Moden
March 10, 2011 at 10:01 am
March 10, 2011 at 11:08 am
Here's another method to accomplish it, utilizing self-joins:
SELECT t1.*,
PrevId = t2.ID,
PrevSub = t2.Sub,
PrevData = t2.DATA,
NextId = t3.Id,
NextSub = t3.Sub,
NextData = t3.DATA
FROM #Data t1
LEFT JOIN #Data t2 ON t1.ID = t2.ID+1
LEFT JOIN #Data t3 ON t1.ID = t3.ID-1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 10, 2011 at 11:16 am
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
create table #Data([ID] [Int],[Sub][Int], [Data][Int])
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (1,1,102)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (2,1,65)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (3,1,85)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (6,1,73)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (7,2,12)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (8,2,234)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (10,2,25)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (11,2,2476)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (12,3,45)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (13,3,5)
CREATE CLUSTERED INDEX ip_tID ON #Data(ID);
SELECT * FROM #Data
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
Thanks for help, have to add one more complication.
What happens when index numbers are missing like at 3 to 6 and 8 to 10??:-)
The reason why I ask, is that its for my blog next and prev page. So when page 2 selected, I can populate a menu with next and prev.
March 10, 2011 at 11:19 am
my query would just show you the next closest value, so you should be all set.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 10, 2011 at 11:23 am
Yes the MAX(ID) does do that, thanks.
This code does not...
SELECT t1.*,
PrevId = t2.ID,
PrevSub = t2.Sub,
PrevData = t2.DATA,
NextId = t3.Id,
NextSub = t3.Sub,
NextData = t3.DATA
FROM #Data t1
LEFT JOIN #Data t2 ON t1.ID = t2.ID+1
LEFT JOIN #Data t3 ON t1.ID = t3.ID-1
OK I am good, thanks all !!:-D
March 10, 2011 at 11:37 am
Digs (3/10/2011)
Yes the MAX(ID) does do that, thanks.This code does not...
SELECT t1.*,
PrevId = t2.ID,
PrevSub = t2.Sub,
PrevData = t2.DATA,
NextId = t3.Id,
NextSub = t3.Sub,
NextData = t3.DATA
FROM #Data t1
LEFT JOIN #Data t2 ON t1.ID = t2.ID+1
LEFT JOIN #Data t3 ON t1.ID = t3.ID-1
OK I am good, thanks all !!:-D
Easily fixed... and probably more efficient than correlated sub-queries.
;
WITH CTE AS
(
SELECT ID, Sub, Data,
RN = ROW_NUMBER() OVER (ORDER BY ID)
FROM #Data
)
SELECT t1.ID, t1.Sub, t1.Data,
PrevId = t2.ID,
PrevSub = t2.Sub,
PrevData = t2.DATA,
NextId = t3.Id,
NextSub = t3.Sub,
NextData = t3.DATA
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RN = t2.RN+1
LEFT JOIN CTE t3 ON t1.RN = t3.RN-1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 10, 2011 at 12:39 pm
Thank you 🙂
March 10, 2011 at 12:51 pm
Welcome!:-D
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 10, 2011 at 1:30 pm
WayneS (3/10/2011)
and probably more efficient than correlated sub-queries.
There's only one way to know, huh? 😉
--Jeff Moden
March 10, 2011 at 1:42 pm
Jeff Moden (3/10/2011)
WayneS (3/10/2011)
and probably more efficient than correlated sub-queries.There's only one way to know, huh? 😉
As usual: It depends...
...and it depends a LOT on the indexing. (The proof is coming, I promise. Trying to nail down the moving parts is like herding cats.)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 10, 2011 at 10:17 pm
Jeff Moden (3/10/2011)
WayneS (3/10/2011)
and probably more efficient than correlated sub-queries.There's only one way to know, huh? 😉
I would have expected Wayne to be right, but in 2K8 - I'm getting the exact opposite. The CSQ is using the index when you have itin place - but the CTE just doesn't seem to gets its act in gear. on a 200K table the CSQ solution returns in 2.4 secs; I cancelled the CTE after 4 minutes.
create table randomSel (id int identity(1,1) primary key clustered, altkey int)
--go
insert randomSel(altkey)
select top (200000) rand(checksum(newid()))*10000 from sys.columns sc1 cross join sys.columns sc2
create index funidx on randomsel(altkey)
set statistics time on
declare @fun int
select @fun=altkey,
@fun=(select max(altkey) from randomSel where altkey < D.altkey),
@fun=(select min(altkey) from randomSel where altkey > D.altkey)
from randomSel d
declare @fun int
;
WITH CTE AS
(
SELECT altkey,
RN = ROW_NUMBER() OVER (ORDER BY altkey)
FROM randomSel
)
SELECT @fun=t1.altkey,
@fun= t2.altkey,
@fun= t3.altkey
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RN = t2.RN+1
LEFT JOIN CTE t3 ON t1.RN = t3.RN-1;
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply