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

kensai
Posting Yak Master

172 Posts

Posted - 2002-04-18 : 10:25:45

I'm coding a "New Topics" page for a bulletin board I'm coding. The page retrieves last 25 topics of every forum. The board has 15 forums. I'm using union and I ended using 15 selects with unions. Now, this does work but I thought I could ask for your opinions if you can think any better way for the New Topics page. I'd be glad if you could share your opinions with me. Here's a sample code I'm using:

select top 25 TopicID,TopicForumNo,TopicTitle,TopicAuthor,TopicDate from TOPICS where TopicForumNo='1'
union
select top 25 TopicID,TopicForumNo,TopicTitle,TopicAuthor,TopicDate from TOPICS where TopicForumNo='2'
....
order by TopicForumNo,TopicID desc

Nazim
A custom title

1408 Posts

Posted - 2002-04-18 : 10:31:02

select top 25 TopicID,TopicForumNo,TopicTitle,TopicAuthor,TopicDate from TOPICS where TopicForumNo in('1' ,'2','3')
order by TopicForumNo,TopicID desc

HTH

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

Edited by - Nazim on 04/18/2002 10:33:44

spock
Starting Member

35 Posts

Posted - 2002-04-22 : 07:34:39

nazim,

quote:

select top 25 TopicID,TopicForumNo,TopicTitle,TopicAuthor,TopicDate from TOPICS where TopicForumNo in('1' ,'2','3')
order by TopicForumNo,TopicID desc

the query you suggested just picks up the top 25 rows for TopicForumNo =1 and will not return any results for topicforumno =2,3 etc..how do we go about doing that?

spock

ToddV
Posting Yak Master

218 Posts

Posted - 2002-04-22 : 08:29:57

This may not perform any better than the union, but it is worth a shot:


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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-22 : 10:46:20

quote:

The page retrieves last 25 topics of every forum.

The union query you included gets up to 25 topics from each forum and orders them by forum and topic id. However, there is no ORDER BY on the SELECTS, so you will not necessarily get the 25 topics with the largest topic id values for each forum. This can be remedied by putting the TOPs into subqueries:

select TopicID,TopicForumNo,TopicTitle,TopicAuthor,TopicDate from (select top 25 * from TOPICS where TopicForumNo='1' order by TopicID desc) AS a
union
select TopicID,TopicForumNo,TopicTitle,TopicAuthor,TopicDate from (select top 25 * from TOPICS where TopicForumNo='2' order by TopicID desc) AS a
....
order by TopicForumNo,TopicID desc

UNION ALL rather than UNION could make it (imperceptibly) faster, and I wonder why a column called TopicForumNo is a string.

If you want to have a go with a more generalized query, I suspect this might get better performance than Todd's (though I doubt either will be as fast as the union approach for this):


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
  )

ToddV
Posting Yak Master

218 Posts

Posted - 2002-04-22 : 10:52:58

Kensai,
I believe each row of the Union query will be unique from all others. If this is true, You can use UNION ALL. That might pick up a little time as well.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-22 : 17:59:28

If you were totally sure about 25 (er, well this example is for 10, but you get the idea) but not about the number of forums, and you were clinically insane, then this one is surprisingly fast!


SELECT fn AS TopicForumNo,
  CASE n
    WHEN 0 THEN id0 WHEN 1 THEN id1 WHEN 2 THEN id2 WHEN 3 THEN id3 WHEN 4 THEN id4
    WHEN 5 THEN id5 WHEN 6 THEN id6 WHEN 7 THEN id7 WHEN 8 THEN id8 WHEN 9 THEN id9
  END AS TopicID
FROM (
    SELECT fn, id0,id1,id2,id3,id4,id5,id6,id7,id8,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id8 AND TopicForumNo = fn) AS id9 FROM (
        SELECT fn, id0,id1,id2,id3,id4,id5,id6,id7,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id7 AND TopicForumNo = fn) AS id8 FROM (
            SELECT fn, id0,id1,id2,id3,id4,id5,id6,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id6 AND TopicForumNo = fn) AS id7 FROM (
                SELECT fn, id0,id1,id2,id3,id4,id5,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id5 AND TopicForumNo = fn) AS id6 FROM (
                    SELECT fn, id0,id1,id2,id3,id4,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id4 AND TopicForumNo = fn) AS id5 FROM (
                        SELECT fn, id0,id1,id2,id3,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id3 AND TopicForumNo = fn) AS id4 FROM (
                            SELECT fn, id0,id1,id2,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id2 AND TopicForumNo = fn) AS id3 FROM (
                                SELECT fn, id0,id1,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id1 AND TopicForumNo = fn) AS id2 FROM (
                                    SELECT fn, id0,(SELECT MAX(TopicID) FROM TOPICS WHERE TopicID<id0 AND TopicForumNo = fn) AS id1 FROM (
                                        SELECT TopicForumNo AS fn, MAX(TopicID) AS id0 FROM TOPICS GROUP BY TopicForumNo
                                      ) a
                                  ) a
                              ) a
                          ) a
                      ) a
                  ) a
              ) a
          ) a
      ) a
  ) a
CROSS JOIN (
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) Numbers
ORDER BY fn, n

TopicTitle, TopicAuthor, TopicDate are omitted for 'clarity'.

[Rob: If you post the contest results, I'll stop writing stuff like this! (maybe)]

Edited by - Arnold Fribble on 04/22/2002 18:03:34

kensai
Posting Yak Master

172 Posts

Posted - 2002-04-23 : 17:14:47

Thanks for the all help. Actually Nazim's code does the trick, it's just what I want. I kinda felt silly when I first saw that simple code against mine :)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-24 : 04:54:01

So you only wanted 25 topics in total after all? Oh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources