Alternatives for union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alternatives for union

Author

Topic

baka
Starting Member

4 Posts

Posted - 2002-05-26 : 10:16:38

I need a stored procedure which should select top 10 messages for a few people. The only solution I could think of was using union like this:

select PostID,Message from Messages where User='Me'
union
select PostID,Message from Messages where User='He'
union
select PostID,Message from Messages where User='She'
...

And so on. This works but it doesn't look good for me and I think it works kinda slow with this much union's. Can you please show me alternate ways for this?

Nazim
A custom title

1408 Posts

Posted - 2002-05-26 : 10:52:06

going thru ur Sql statement , this can be simply converted using in.

select PostID,Message from Messages where UseR IN('Me' 'She','He')

--------------------------------------------------------------

baka
Starting Member

4 Posts

Posted - 2002-05-28 : 05:26:05

Nazim, I tried that code but it only retrieves 10 messages in total, not 10 messages per user.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-05-28 : 06:49:34

You can use union all which should bestow some improvement performancewise.

Nazim
A custom title

1408 Posts

Posted - 2002-05-28 : 08:58:18

Ah! i got this for you(And this wasnt a quick search

)
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=15054

--------------------------------------------------------------

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-05-28 : 09:16:30

But ignore my big nested query in that thread -- I was just being silly.

Nazim
A custom title

1408 Posts

Posted - 2002-05-28 : 09:22:19

i often wonder, how do you come up with such wacky query's.

Man! you have lot of patience and lot of creativity with you.

About Me! though i know using * in query's isnt good for performance am too lazy to type those column names:(.

--------------------------------------------------------------

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-28 : 09:39:22

Nazim, here is a present for you . . . never use * again . . .


set nocount on
use myatabase

declare @collist varchar(8000)

select
    @collist = coalesce(@collist + ',','') + column_name
from
    information_schema.columns
where
    table_name = 'mytable'

-- for formatting
select
    '            ' + replace(@collist,',',',' + char(13) + '            '

<O>

Nazim
A custom title

1408 Posts

Posted - 2002-05-28 : 09:52:12

Thanx Jay, it was a nice gift

Am pleased, how i wish to give you three wishes so that you can ask for anything. but, alas! i dont have magical power

.

--------------------------------------------------------------

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-28 : 10:39:44

Damn Page47... Hope you don't mind if I use that little pearl of wisdom there, save me alot of typing

baka
Starting Member

4 Posts

Posted - 2002-05-29 : 07:59:36

Nazim, thanks a lot for the search I appreciate that. I found that topic before posting this topic too but I thought it wasn't for me, kensai wants only 25 messages in total. I checked the thread again but I couldn't see which code I should use. Do you think Arnold Fribble's code would be good for me?

Nazim
A custom title

1408 Posts

Posted - 2002-05-29 : 08:46:46

There are two query's

This one is written by Toddv
Select A.TopicID,
A.TopicForumNo,
A.TopicTitle,
A.TopicAuthor,
A.TopicDate
FROM TOPICS A
JOIN Topics B
ON A.TopicForumNo = B.TopicForumNo AND
A.TopicDate<=B.TopicDate
GROUP BY A.TopicID,
A.TopicForumNo,
A.TopicTitle,
A.TopicAuthor,
A.TopicDate
Having Count(*) <=25

This is the one Arnold Fribble wrote and this is the one i was refering to

SELECT TopicID, TopicForumNo, TopicTitle, TopicAuthor, TopicDate
FROM TOPICS AS T1
WHERE TopicID IN (
SELECT TOP 25 TopicID
FROM TOPICS AS T2
WHERE T2.TopicForumNo = T1.TopicForumNo
ORDER BY TopicID
)

NOTE: All rights of the query's lie with the respective owners any unauthorized viewing and copies is a punishable act according to the law of the land

(you dont worry baka, by posting on this thread , Arnold has given his consent

--------------------------------------------------------------

baka
Starting Member

4 Posts

Posted - 2002-05-31 : 17:02:11

I got it Nazim, thanks a lot for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources