Stored Procedure - TOP/UNION clauses 
Author Message
 Stored Procedure - TOP/UNION clauses

Hi, does anybody know how I would select the top 5 results from the
following SQL Stored Procedure?


SET NOCOUNT ON

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid
join note_recipients on notes.noteid=note_recipients.noteid where

union

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid

union

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid

order by notes.date desc
GO

I tried the following modification to the above stored proc w/o
success:


SET NOCOUNT ON

select top 5 from (
select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid
join note_recipients on notes.noteid=note_recipients.noteid where

union

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid

union

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid

order by notes.date desc) as top_5_notes
GO

Thanks in advance!

Darren



Sat, 20 Mar 2004 12:40:57 GMT
 Stored Procedure - TOP/UNION clauses

almost there...


SET NOCOUNT ON

select top 5 *
from (
select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid
join note_recipients on notes.noteid=note_recipients.noteid where

union

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid

union

select distinct notes.noteid, username, subject, date, viewed from
members join notes on members.userid=notes.senderid

order by notes.date desc) as top_5_notes
GO


Quote:
> Hi, does anybody know how I would select the top 5 results from the
> following SQL Stored Procedure?

> CREATE PROCEDURE [dbo].[asianconnexions_get_notes]

> SET NOCOUNT ON

> select distinct notes.noteid, username, subject, date, viewed from
> members join notes on members.userid=notes.senderid
> join note_recipients on notes.noteid=note_recipients.noteid where

> union

> select distinct notes.noteid, username, subject, date, viewed from
> members join notes on members.userid=notes.senderid

> union

> select distinct notes.noteid, username, subject, date, viewed from
> members join notes on members.userid=notes.senderid

> order by notes.date desc
> GO

> I tried the following modification to the above stored proc w/o
> success:

> CREATE PROCEDURE [dbo].[asianconnexions_get_notes]

> SET NOCOUNT ON

> select top 5 from (
> select distinct notes.noteid, username, subject, date, viewed from
> members join notes on members.userid=notes.senderid
> join note_recipients on notes.noteid=note_recipients.noteid where

> union

> select distinct notes.noteid, username, subject, date, viewed from
> members join notes on members.userid=notes.senderid

> union

> select distinct notes.noteid, username, subject, date, viewed from
> members join notes on members.userid=notes.senderid

> order by notes.date desc) as top_5_notes
> GO

> Thanks in advance!

> Darren



Sat, 20 Mar 2004 12:56:47 GMT
 Stored Procedure - TOP/UNION clauses

Quote:
>> select top 5 from (

select top 5 * from (

    Also, all those DISTINCT is really unnecessary when doing UNION. It is
redundant since the UNION will eliminate the duplicates anyway.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Sat, 20 Mar 2004 13:11:57 GMT
 Stored Procedure - TOP/UNION clauses
Thanks OJ, Umachandar for your timely responses!

One last question I just thought up:

Is it possible to select a subset of your records that isn't in the top 5?  For instance, would I be able to select records 6-10?  I guess the only way to do that would be by adding a column (in the table containing the records) for the "record number", and then adding a where clause in the select statement right?

Thx again!

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 21 Mar 2004 01:31:34 GMT
 Stored Procedure - TOP/UNION clauses
the easiest way would be...

1. create a temp table with identity(1,1)
2. insert into it with, say your top 10
3. select from it with id between 6 and 10

-oj


Thanks OJ, Umachandar for your timely responses!

One last question I just thought up:

Is it possible to select a subset of your records that isn't in the top 5?
For instance, would I be able to select records 6-10?  I guess the only way
to do that would be by adding a column (in the table containing the records)
for the "record number", and then adding a where clause in the select
statement right?

Thx again!

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 21 Mar 2004 01:38:11 GMT
 Stored Procedure - TOP/UNION clauses

Hi OJ

Thanks for that novel idea!  You're the best!

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 21 Mar 2004 05:51:42 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Store procedure--TOP Clause parameter

2. TOP 1 without TOP clause USING GROUP BY

3. union within a stored procedure

4. union results of stored procedures

5. union within a stored procedure

6. UNION two stored procedure result sets

7. Stored procedures and UNION (SQL Server 2000)

8. Using UNION with Stored Procedure

9. Stored procedures and union joins

10. UNION and stored procedures

11. Top keyword in stored procedure


 
Powered by phpBB® Forum Software