SELECT TOP (select distinct count(state) from pubs..authors)
* FROM pubs..authors

CREATE PROC TopTitles(@count INT)
AS
SELECT TOP (@count) title FROM pubs..titles
ORDER BY ytd_sales


CREATE 
--ALTER
PROC TopTitles2(@count INT)
AS
SET ROWCOUNT @count
SELECT title FROM pubs..titles
ORDER BY ytd_sales
SET ROWCOUNT 0


select * from pubs..titles

CREATE PROC TopPublishers(@count INT)
AS
SELECT pub_name FROM pubs..publishers
WHERE pub_id 
IN (SELECT TOP(@count) pub_id FROM pubs..titles
ORDER BY ytd_sales)


CREATE PROC TopTitlesTies(@count INT)
AS
SELECT TOP (@count) WITH TIES
title FROM pubs..titles
ORDER BY ytd_sales



EXEC TopPublishers 3

EXEC TopTitles2 4
EXEC TopTitlesTies 4


CREATE TABLE TestData
(i INT )

DECLARE @index INT
SET @index = 1000
ins:
INSERT INTO TestData VALUES (@index)
SET @index = @index - 1
IF @index <> 0 GOTO ins

DECLARE @block INT
SET @block = 10
del:
DELETE TOP (10) TestData WHERE i > 500
IF @@ROWCOUNT > 0 GOTO del

select count(*) from TestData




exec TopTitles2 3
exec TopTitles 3


CREATE 
--ALTER
PROC TopAuthors(@count INT)
AS
SELECT TOP(@count) WITH TIES
A.au_fname, A.au_lname FROM pubs..authors AS A
JOIN pubs..titleauthor AS TA ON A.au_id = TA.au_id
JOIN pubs..titles AS T ON T.title_id = TA.title_id
ORDER BY T.ytd_sales  


exec topauthors 3


exec toptitles 3

select TOP 3 * from pubs..titles

SET ROWCOUNT  0

