November 4, 2003 at 11:19 pm
BOL 2000:
WITH TIES
Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an ORDER BY clause is specified.
Get the first N rows of the ordered list. If the N+1 row's value is the same the last row value in the ordered list of N values output them aswell, regardsless how many duplicates there are in the rest of the list.
SET NOCOUNT ON
DECLARE @TTT TABLE (THE_ID INT NOT NULL,Name varchar(50))
INSERT INTO @TTT VALUES(1,'A');
INSERT INTO @TTT VALUES(2,'BB');
INSERT INTO @TTT VALUES(2,'B');
INSERT INTO @TTT VALUES(3,'C');
INSERT INTO @TTT VALUES(3,'D');
INSERT INTO @TTT VALUES(3,'DD');
INSERT INTO @TTT VALUES(3,'DDD');
INSERT INTO @TTT VALUES(3,'DDDD');
INSERT INTO @TTT VALUES(4,'E');
INSERT INTO @TTT VALUES(5,'F');
INSERT INTO @TTT VALUES(6,'G');
SET NOCOUNT OFF
SELECT TOP 4 THE_ID FROM @TTT ORDER BY 1;
THE_ID
-----------
1
2
2
3
(4 row(s) affected)
SELECT TOP 4 WITH TIES THE_ID FROM @TTT ORDER BY 1;
THE_ID
-----------
1
2
2
3
3
3
3
3
(8 row(s) affected)
SELECT DISTINCT TOP 4 THE_ID FROM @TTT ORDER BY 1;
THE_ID
-----------
1
2
3
4
(4 row(s) affected)
November 5, 2003 at 1:42 am
there are multiple reasons to not use a dynamic sql string with execSQL, among the most obvious are permissions (granting universal select permission on tables is generally considered a poor practice) and performance (query msut be parsed and query plan generated with each excution.)
I have found a similar solution to this same problem, with the same assumption already espoused here: the state must change once the records are processed.
create procedure foo(@numRecs int) as
set @@ROWCOUNT @numRecs
select
col1,
col2,
etc
from myTable
set @@ROWCOUNT 0
/* while this will be reset when the sproc exits this is a good coding practice to clean up */
go
Good luck. Ra!
-- Alex
November 5, 2003 at 5:57 am
On the topic of Paging (ie getting the next 10 records) - the links seem rather limiting - how about: (using pubs)
-- First 10
select top 10 * from employee
order by lname
-- Next 10
select top 10 * from employee
where emp_id not in (select top 10 emp_id from employee order by lname)
order by lname
-- Next 10
select top 10 * from employee
where emp_id not in (select top 20 emp_id from employee order by lname)
order by lname
etc
I tried this on a table with 170k records and it runs in under 1 sec
November 5, 2003 at 7:00 am
Why not terminate your loop after 40 and why all the cursors?
DECLARE @temp nvarchar(200)
DECLARE @email_campaign_id as int,
@status int,
@start_datetime datetime,
@email_max_quantity int,
@email_quantity int,
@email_subject nvarchar(50),
@email_message nvarchar(4000)
SELECT @email_campaign_id = email_campaign_id,
@status = status,
@start_datetime = start_datetime,
@email_max_quantity = email_max_quantity,
@email_quantity = email_quantity,
@email_subject = email_subject,
@email_message = email_message
FROM (SELECT TOP 1 email_campaign_id,
status,
start_datetime,
email_max_quantity,
email_quantity,
email_subject,
email_message
FROM tblEmailCampaign
WHERE (status = 1) AND (approved = 1)
ORDER BY start_datetime ASC) a
IF ISNULL(@status,0) = 1
BEGIN
END
DECLARE @cnt as int
DECLARE @contact_id as int, @email_address nvarchar(255)
SET @cnt = 0
DECLARE record_cursor CURSOR FOR
SELECT contact_id, ISNULL(email_address, '') AS formatted_email_address
FROM tblContact
WHERE (LEN(ISNULL(email_address, '')) > 0)
AND (internal_contact = 0)
AND (email_rejected = 0)
AND (email_marketing = 1)
AND (request_for_removal = 0)
ORDER BY ISNULL(email_address, '')
OPEN record_cursor
FETCH NEXT FROM record_cursor INTO @contact_id, @email_address
WHILE (@@fetch_status = 0) AND (@cnt < @email_max_qty)
BEGIN
FETCH NEXT FROM record_cursor INTO @contact_id, @email_address
SET @cnt = @cnt + 1
END
CLOSE record_cursor
DEALLOCATE record_cursor
set @temp = CAST(@cnt AS nvarchar(1000))
EXEC master.dbo.xp_sendmail
@recipients = 'Mike',
@subject = 'TEST EMAIL',
@message = @temp
END
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy