SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Optimizing Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/09/2011 :  18:05:54  Show Profile  Reply with Quote
Who is ready to offer a most efficient approach for matching next rows with current rows (based on a sequential value with gaps in them)?
Is following query most efficient or you have a better query?

WITH C AS
(
	SELECT *, ROW_NUMBER() OVER(ORDER BY date ASC) rn
          FROM TableName
)
SELECT *
  FROM C A
       JOIN C B
         ON A.rn + 1 = B.rn;


______________________

Edited by - ms65g on 04/09/2011 18:46:57

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/10/2011 :  15:24:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It depends what the query is for. Weighted median? Other purpose?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 04/10/2011 :  16:47:26  Show Profile  Reply with Quote
Can we suppose just the result is important for us, no dive to a specific problem?
So if you ready, it’s the table with some sample data and the desired result:

CREATE TABLE [Sample] 
(
	seq_nbr INTEGER NOT NULL PRIMARY KEY
);

INSERT INTO [Sample]
VALUES (1), (5), (7), (8), (15), (50);

SELECT seq_nbr
  FROM [Sample]
 ORDER BY seq_nbr ASC;

/*====== Sample Data =========
seq_nbr
-----------
1
5
7
8
15
50


============ Wanted Result ==========
seq_nbr     seq_nbr
----------- -----------
1           5
5           7
7           8
8           15
15          50
50          NULL

*/


And my first attempt here:
WITH C(seq_nbr, rnk) AS
(
	SELECT seq_nbr,
	       ROW_NUMBER() OVER(ORDER BY seq_nbr ASC)
	  FROM [Sample]
)
SELECT A.seq_nbr, B.seq_nbr
  FROM C AS A 
       LEFT JOIN C AS B
         ON A.rnk + 1 = B.rnk;


______________________
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/11/2011 :  03:45:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
As I wrote before. It all depends on the usage of the data.
Anyway, here is another approach from the top of my head that has less reads.

SET STATISTICS IO ON
SET STATISTICS TIME ON

-- Ms65g
;WITH C(seq_nbr, rnk) AS
(
	SELECT seq_nbr,
	       ROW_NUMBER() OVER(ORDER BY seq_nbr ASC)
	  FROM [Sample]
)
SELECT A.seq_nbr, B.seq_nbr
  FROM C AS A 
       LEFT JOIN C AS B
         ON A.rnk + 1 = B.rnk;

-- Peso
SELECT		s.seq_nbr,
		f.seq_nbr
FROM		dbo.[Sample] AS s
OUTER APPLY	(
			SELECT TOP(1)	w.seq_nbr
			FROM		dbo.[Sample] AS w
			WHERE		w.seq_nbr > s.seq_nbr
			ORDER BY	w.seq_nbr
		) AS f

SET STATISTICS TIME OFF
SET STATISTICS IO OFF



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/11/2011 :  08:50:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I do not like that you take my suggestions from elsewhere and put them here under my name when the original solutions were meant to be used in another scenario.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/11/2011 :  08:53:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by ms65g

As you see my second solution is most efficient and needs half of your best number of reads.
Yes, but your suggestion only handles seq_nbr column only. My suggestion handles ALL columns, IF they are going to used further.

As I have told you, the solution varies for the purpose of the data.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/11/2011 :  09:06:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is my own "Peso 2"
;WITH cte
AS (
	SELECT		seq_nbr,
			d,
			ROW_NUMBER() OVER (ORDER BY seq_nbr) / 2 AS theGrp
	FROM		[Sample]
	CROSS JOIN	(
				VALUES	(0),
					(1)
			) AS x(d)
)
SELECT		MIN(seq_nbr) AS col1,
		MAX(seq_nbr) AS col2
FROM		cte
GROUP BY	theGrp
HAVING		MAX(d) = 1
And here is the statistics between your #2 and my #2 when using more sample data, or example 2514 records.

-- Peso 2 (my own version)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
Table 'Sample'. Scan count 1, logical reads 7, physical reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 53 ms.


-- Ms65g #2
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
Table 'Sample'. Scan count 1, logical reads 14, physical reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 69 ms.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000