RETURNING TOP X RECORDS
-
Is it possible to return for example the first 40 records of a dataset ?
I have 13,000 records to process, except I want to look at the first 40 records every hour within a scheduled SQL job. When I have done processing these 40 I will mark a flag to denote they have been looked at, the next time it runs it will pick the next 40 and so on.
Is there a TSQL TOP command I can use ?
Cheers
Mike
-
quote:
Is there a TSQL TOP command I can use ?Yes, it is
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank's being funny! But he is correct.
SELECT TOP 40 column1, column2.....
or even
SELECT TOP 40 WITH TIES column1, column2
-SQLBill
-
Actually I think Mike doesn't want always the same 40 records to be returned. Looks like he want some kind of 'paging'.
I'll post a link when I find it!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Superb...
Thank you all for you help...
Mike
-
Have a look at this post to see another way of stepping through a record set:
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=11057
Jeremy
-
Another reread!
When the first 40 rows are marked as something, the next to be retrieved lack this mark, a simple TOP statement will work.
<pseudo SQL>
SELECT TOP 40 blahbla FROM table WHERE column is not marked ORDER BY column.
</pseudo SQL>
Remember to use ORDER BY or you *might* get strange results.
Anyway, here's a link on paging
http://www.microsoft.com/sql/techinfo/tips/administration/resultset.asp
Btw, how does WITH TIES work?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
umph...it definitely takes too much time for me to answer
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ok...
I now have the number of the records I want to return in a variable called @email_max_qty (value = 40)
So I need to replace the following:
DECLARE record_cursor CURSOR FOR
SELECT TOP <B>40</B> company_name
FROM tblAddress
WHERE country_code='GB'
ORDER BY address_id
OPEN record_cursor
etc. etc.
How do I replace the TSQL script above substituting 40 for @email_max_qty ?
Cheers.
Mike
-
quote:
DECLARE record_cursor CURSOR FORSELECT TOP <B>40</B> company_name
FROM tblAddress
WHERE country_code='GB'
ORDER BY address_id
OPEN record_cursor
..
How do I replace the TSQL script above substituting 40 for @email_max_qty ?
..
DECLARE @MyCount char(2)
DECLARE @stmt NVARCHAR(200)
SET @MyCount = '40'
SET @stmt = 'SELECT TOP ' +@MyCount+' companyname FROM tblAddress
WHERE country_code='GB'
ORDER BY address_id'
EXEC sp_ExecuteSQL @stmtWhy do you use a cursor?
Frank
Edited by - Frank Kalis on 11/04/2003 08:12:17 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My Scripts looks like this :
DECLARE @sql nvarchar(200), @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)
DECLARE record_cursor CURSOR FOR
SELECT 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
OPEN record_cursor
FETCH NEXT FROM record_cursor INTO @email_campaign_id, @status, @start_datetime, @email_max_quantity, @email_quantity, @email_subject, @email_message
CLOSE record_cursor
DEALLOCATE record_cursor
IF @status = 1
/*
We have an active email campaign, determine the audience.
Limit the number of emails to be sent out to the value of the variable: @email_max_quantity
*/
BEGIN
DECLARE @cnt as int
DECLARE @contact_id as int, @email_address nvarchar(255)
/*
This SQL script needs 40 replacing with the variable name @email_max_quantity
*/
DECLARE record_cursor CURSOR FOR
SELECT TOP 40 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, '')
SET @cnt = 0
OPEN record_cursor
FETCH NEXT FROM record_cursor INTO @contact_id, @email_address
WHILE @@fetch_status = 0
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
-
Frank (and others who may care),
Here's the difference between TOP N and TOP N WITH TIES
Data:
1
2
2
3
4
5
6
SELECT TOP 3 Data
FROM mytable
ORDER BY Data ASC
Results:
1
2
2
SELECT TOP 3 WITH TIES Data
FROM mytable
ORDER BY Data ASC
1
2
2
3
The first (without TIES) takes the first N rows based on the ORDER BY. In this case, it's the row with 1 and the two rows with 2's.
The second (WITH TIES) takes the first N rows, but counts rows that are the same (WITH TIES) as being ONE row. So both rows with a 2 are counted as one row.
-SQLBill
-
Thanks SQLBill, I couldn't figure that out from BOL. Seems pretty useful
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How does WITH TIES know what is a tie? The SELECT statements below return the same results regardless if you use WITH TIES or not.
DECLARE @TTT TABLE (THE_ID INT NOT NULL)
INSERT INTO @TTT VALUES(1);
INSERT INTO @TTT VALUES(2);
INSERT INTO @TTT VALUES(2);
INSERT INTO @TTT VALUES(3);
INSERT INTO @TTT VALUES(4);
INSERT INTO @TTT VALUES(5);
INSERT INTO @TTT VALUES(6);
SELECT TOP 3 * FROM @TTT ORDER BY 1;
SELECT TOP 3 WITH TIES * FROM @TTT ORDER BY 1;
-
Unfortunately the example given doesn't illustrate this correctly. Try the following instead:
DECLARE @TTT TABLE (THE_ID INT NOT NULL)
INSERT INTO @TTT VALUES(1);
INSERT INTO @TTT VALUES(2);
INSERT INTO @TTT VALUES(3);
INSERT INTO @TTT VALUES(3);
INSERT INTO @TTT VALUES(4);
INSERT INTO @TTT VALUES(5);
INSERT INTO @TTT VALUES(6);
SELECT TOP 3 * FROM @TTT ORDER BY 1;
SELECT TOP 3 WITH TIES * FROM @TTT ORDER BY 1;
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply