Last Row in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Last Row in Query

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 09:59:47

It saves a lot of application coding if we can output "LAST" (and sometimes "FIRST") as a column value

Typically we have

INSERT INTO @Temp
SELECT Col1, Col2
FROM MyTable1
WHERE Col1 = 'FOO' AND Col2 = 'BAR'

and then we may delete some rows from @temp, or INSERT some more ... and eventually we get to


SELECT ...
FROM @Temp AS T
     JOIN MyTableX AS X
        ON X.ID = T.ID
ORDER BY T.Col1, X.Col5

I could do:


SELECT TOP 1 @Col1 = T.Col1, @Col5 = X.Col5
FROM @Temp AS T
     JOIN MyTableX AS X
        ON X.ID = T.ID
ORDER BY T.Col1 DESC, X.Col5DESC

and then


SELECT ...,
     CASE WHEN T.Col1 = @Col1 AND X.Col5 = @Col5 THEN 'Last' END AS [LastMarker]
FROM @Temp AS T
     JOIN MyTableX AS X
        ON X.ID = T.ID
ORDER BY T.Col1, X.Col5

but I expect there is a better way using ROW_NUMBER() OVER?

2) We sometimes need "Is this different to previous row?" too

Assume we do all the above prep of @Temp and then (under SQL 2000) we did


DECLARE @Temp2 TABLE
(
    [T_Identity] int IDENTITY(int, 1, 1) NOT NULL,
    [T_ID] ...
    [Col1] ...
)

INSERT INTO @Temp2
SELECT T.ID, Col1, ...
FROM @Temp AS T
     JOIN MyTableX AS X
        ON X.ID = T.ID
ORDER BY T.Col1, X.Col5

and then we can compare with previous row in the "output" statement:


SELECT ...,
     CASE WHEN T2_CURRENT.Col1 = T2_PREV.Col1 THEN 'Same' ELSE 'Changed' END AS [HasChangedMarker]
FROM @Temp2 AS T2_CURRENT
     LEFT OUTER JOIN @Temp2 AS T2_PREV
         ON T2_PREV.T_Identity = T2_CURRENT.T_Identity - 1
     ...
ORDER BY T2.T_Identity

there must be a smarter way in SQL 2008?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-15 : 19:19:17

Does this help?[CODE]
SELECT ...,
CASE WHEN a.rn = 1 THEN 'Last' else '' END AS [LastMarker]
FROM
(
select ...,
row_number() over (order by T.Col1 DESC, X.Col5DESC) rn
from
@Temp AS T
JOIN MyTableX AS X
ON X.ID = T.ID
) a[/CODE]

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 05:19:01

Doh! Forgot about using DESC on the ROW_NUMBER() OVER - thanks, that did the trick

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-16 : 05:33:07

quote:
Originally posted by Kristen

Doh! Forgot about using DESC on the ROW_NUMBER() OVER - thanks, that did the trick

Seems you have not TESTed row_number() fully

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 06:25:19

Actually I have, which makes it all the more annoying!

I started with the really useful examples from your Blog, and built up a file of examples (bit more complex than yours, so they are closer to my real-world needs) ... but I don't seem to be able to get my head around ROW_NUMBER() OVER to be able to instinctively "put my finger" on how to use it best.

However, I've been thinking:

Because of the DESC sort in ROW_NUMBER OVER and then an ASCending sort on the outer Select: I wonder if instead of:


SELECT ...,
     CASE WHEN a.rn = 1 THEN 'Last' else '' END AS [LastMarker]
FROM 
	(
	select ...,
		row_number() over (order by T.Col1 DESC, X.Col5 DESC) rn
	from
		@Temp AS T
		JOIN MyTableX AS X
			ON X.ID = T.ID
	) a
order by T.Col1 ASC, X.Col5 ASC

this may be more efficient:


SELECT @intRowCount = COUNT(*) FROM @Temp  [cyan]-- Will be less than 100[/cyan]
SELECT ...,
     CASE WHEN a.rn = @intRowCount THEN 'Last' else '' END AS [LastMarker]
FROM 
	(
	select ...,
		row_number() over (order by T.Col1 ASC, X.Col5 ASC) rn
	from
		@Temp AS T
		JOIN MyTableX AS X
			ON X.ID = T.ID
	) a
order by T.Col1 ASC, X.Col5 ASC

so the Sort Orders are both ASCending - or even make the final sort order

order by a.rn ASC

what do you think?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-16 : 07:00:28

Try both and check the execution plans. This may be give a better plan

;WITH cteYak
AS (
	SELECT		... ,
			ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.Col1, x.Col5) AS RowNum,
			COUNT(*) OVER (PARTITION BY t.ID) AS cntNum
	FROM		@Temp AS t
	INNER JOIN	dbo.MyTableX AS x ON x.ID = t.ID
)
SELECT		... ,
		CASE
			WHEN RowNum = cntNum THEN 'Last'
		END AS [LastMarker]
FROM		cteYak
ORDER BY	Col1,
		Col5

N 56°04'39.26"
E 12°55'05.63"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-06-16 : 07:25:11

COUNT(*) OVER usually counts it in the temp table and increases the reads. that's why i don't like the aggregates in the OVER

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 07:30:33

Yeah, you are right, TEST is good! and would provide the answer.

The actual code has

IF EXISTS (SELECT * FROM @temp)
BEGIN
... do the SELECT above ...

so replacing that with a

SELECT @intRowCount = COUNT(*) FROM @temp
IF @intRowCount> 0
BEGIN

and then using @intRowCount in the CASE statement as well is probably not that horrific ...

Hadn't thought to use CTE, thanks ... to many choices once languages become feature-rich!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-16 : 07:34:49

quote:
Originally posted by spirit1

COUNT(*) OVER usually counts it in the temp table and increases the reads.

You mean a worktable?

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 08:04:44

"COUNT(*) OVER usually counts it in the temp table and increases the reads. that's why i don't like the aggregates in the OVER"

HiYa Spirit

Useful insight, thanks.

But a ROW__NUMBER() OVER in DESCending order, and then Order the main Select in ASCending order strikes me as more than one SCAN too ...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-06-16 : 08:59:23

@Peso:
yes worktable. as it's in tempdb anyway i use the term temp table.

@Kristen:
row_number, rank, dense_rank and ntile are NOT aggregate functions and thus use only 1 pass through.
sum, count, max, etc.. are all aggregates so they use intermediate temp tables for group aggregations.
don't confuse the two.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-16 : 11:31:39

the order of data stored in a database has no meaning

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 13:44:40

Yeah, I wasn't confusing the two, just worrying about generating a ROW_NUMBER OVER using DESCending sort order on a @Temp table, where I might be able to the use the PK effectively, and then asking for the final output to use ASCENDING sort order - which may require an addition step (unless SQL traverses the PK backwards I suppose?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources