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