Except 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)

Except query

Author

Topic

seware
Starting Member

11 Posts

Posted - 2011-09-21 : 15:09:25

An initial request from marketing was a Top 500 customer report, which I delivered. Then they asked for a Next 2500 Customer Report as a separate delivery.

I have delivered this by running the same query twice, once for the Top 3000 and next for the Top 500 with an 'Except" in the middle.

For example:

SELECT udf_TopCustomers(3000)

EXCEPT

SELECT udf_TopCustomers(500)

FROM...
WHERE...

This works but seems kludgy. Is there a better way to do this? (A TSQL clause like "MIDDLE(501,3000)" would be heplful here.)

Thanks all.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-09-21 : 15:38:19

Where NOT IN (Select Top 500 from your table)

Would that not work?

Everyday life brings me back to reality

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 15:41:58

Denali has an enhanced ORDER BY clause which makes this easy (finally).

But in 2008, one option is to use ROW_NUMBER(). Something like;


SELECT <your columns>
FROM 
(
	SELECT ROW_NUMBER() OVER(ORDER BY id) RowNbr, id 
	FROM <your results>
) t
WHERE RowNbr BETWEEN @startingRow AND @endingRow

So the udf would add these two input params (@startingRow and @endingRow).

HTH.

seware
Starting Member

11 Posts

Posted - 2011-09-21 : 23:17:51

Thanks both.... ehorn I like your solution... will put both to the performance test tonight (because I like stats...)

Thanks again.

~The accidental DBA

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 23:20:59

yvw,

If you would, let us know how performance looks.

Have a nice evening.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources