Using UNION Select Top 5 *

User Northwind

select top 5 * from customers where city = 'London'
Union
select top 5 * from customers where city = 'Sao Paulo'
order by city desc

If I execute the above query it is showing me 9 records,
which includes (5 'London' + 4 'Sao Paulo').
Instead of 9 I want to display only 5 records in descending order on city.
So it should show me (4 'Sao Paulo' + 1 'London')



Surendra Kalekar

Or you could use SET ROWCOUNT 5 and put your statements in the reverse order so Sao Paulo comes first. If these are really the only 2 you have and this wasn't just an example that is really more complicated:

SET ROWCOUNT 5
select * from customers where city = 'Sao Paulo'
union
select * from customers where city = 'London'
SET ROWCOUNT 0


Using this will also improve your performance because once the ROWCOUNT reaches its limit it will not bother to lookup values period. Meaning if you had 5 Sao Paulo cites it would never actually try and pull any values for London. Whereas the other approach pulls them and then just doesn't bother bringing them back to the client.

I just put this in for fun, because I assume your query is probably really more complicated than this.

Problem with your request is that no query will necessarily show you customers from London: only if there are less than 5 customers in Sao Paulo.

The problem is that it isn't really clear what you want to show, and why. In any case this would require a complex query - and rather a pointless one at that.

W/the limited amount of info you've provided, I agree w/druer. The only exception is that I'd go w/union all instead of union...


set rowCount 5
select * from customers where city = 'Sao Paulo'
union all
select * from customers where city = 'London'
set rowCount 0

That's what I get for copying/pasting the original query. (I do know better)

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />In any case this would require a complex query - and rather a pointless one at that.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks everybody for your responce.<br /><b>Adriaan</b>, This query is not pointless [<img src='/community/emoticons/emotion-1.gif' alt='' />] this is required for the trial subscribers. We have one SP, which is having union of two queries and paging logic and will display 25 rows on each page. If it is trial subscriber we want to display only top 25 rows.<br /><br /><br /><h6>Surendra Kalekar</h6>

quote:Originally posted by Madhivanan

>>The only exception is that I'd go w/union all instead of union...

Yes. Union All is more efficient that UNION as UNION will unnecessarily select distinct and order the resultset


Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan and Null.... Thanks I was unknow about it.
Can you provide me some good link which will explain with example how UNION ALL is better. Meanwhile I will also try to find it out.


Surendra Kalekar

quote:Originally posted by Madhivanan

>>The only exception is that I'd go w/union all instead of union...

Yes. Union All is more efficient that UNION as UNION will unnecessarily select distinct and order the resultset


Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan and Null.... Thanks I was unknow about it.
Can you provide me some good link which will explain with example how UNION ALL is better. Meanwhile I will also try to find it out.


Surendra Kalekar

To come back to the 'pointless' bit, from your original post: "Instead of 9 I want to display only 5 records in descending order on city. So it should show me (4 'Sao Paulo' + 1 'London')"

Guess I must have been hypersensitive to the 'should show' phrase: if you read that as 'must show 4 from Sao Paulo and 1 from London' it would be a pretty ugly query indeed.

Don't bother with UNION here, unless you test and find that it performs better than WHERE city = 'London' OR city = 'Sao Paulo'.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />To come back to the 'pointless' bit, from your original post: "Instead of 9 I want to display only 5 records in descending order on city. So it should show me (4 'Sao Paulo' + 1 'London')"<br /><br />Guess I must have been hypersensitive to the 'should show' phrase: if you read that as 'must show 4 from Sao Paulo and 1 from London' it would be a pretty ugly query indeed.<br /><br />Don't bother with UNION here, unless you test and find that it performs better than WHERE city = 'London' OR city = 'Sao Paulo'.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hey Adriaan coooool..[<img src='/community/emoticons/emotion-1.gif' alt='' />] just forget that phrase and don't take it seriously in my future posts I will avoid such phrases. It could be better if I paste my code here instead of this example. <br />Anyway I changed that sp is working fine. Thanks once again to everybody.<br /><br /><br /><h6>Surendra Kalekar</h6>

Surendra, I'm chilling [8D] ...

quote:Originally posted by surendrakalekar
Hi Madhivanan and Null.... Thanks I was unknow about it.
Can you provide me some good link which will explain with example how UNION ALL is better. Meanwhile I will also try to find it out.

Well Madhavinan and ghemant gave you the jist of it...but here...

http://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp

http://www.sql-server-performance.com/transact_sql.asp
--search on "union", there are a couple of segments that discuss union vs. union all

I've personally seen some awesome perforance gains from using union all vs. union. The sad part is alot of db programmers (well, @least the ones that don't put forth much effort in writing efficient code) I've talked to overlook the benefits of union all...and some haven't even heard of it (not always a bad thing, like yourself, maybe they've just never been exposed to it).

I think that the problem is that the normal expectation would be that the default for UNION would be ALL. Since the very nature of using a UNION is similar to saying A + B. Programmers say I want this set of results, and oh by the way also include this set of results. SELECT also has ALL or DISTINCT but the default for it is obviously ALL. So it would only make sense if you say SELECT ALL of these and/UNION SELECT ALL of these that you would get back ALL of the records selected. Of course it doesn't, which is why the pay us the big bucks. Yeah right!

As you say, many have never heard of it, because they've gotten away without needing it. Usually the first set doesn't return any rows that would be even close to the second set so a DISTINCT default lets them get through. As many don't really worry about performance either, they would never notice the performance gain of using ALL. Funny thing is that I now maintain a database from a 3rd party vendor. Just last week I came across a stored procedure that does some bizarre (very poorly coded T-SQL) things and takes about 3 minutes minimum to perform. I was able to rewrite the stored procedure to run in less than 20 seconds, but oddly enough the only thing that they had coded properly was the UNION ALL. What was funny is that they were selecting completely distinct data from 3 different tables, and unlike many other stored procedures that they had written with just UNION, they actually had it spelled out in this case. So in the end the piece that took less than .02% of the overall time to complete was the only piece that was written and performed well.

quote:Originally posted by Adriaan
Don't bother with UNION here, unless you test and find that it performs better than WHERE city = 'London' OR city = 'Sao Paulo'.

I did test the diff approaches, on a few different criteria. The approach w/Union All should peform better...well, @least it did in every instance when I tested it. The difference being that w/out the union all, he'd have to include an order by. If I have time today I'll put some script up.

druer,

You're right on, most the guys I've talked to never found a "need" to use Union All. Many of our developers are just after the dang "result set", and don't really bother how they get it, then they wonder why their stuff runs terribly. Check out this snip of code I dug out (pseudo form)...this was one of many qry's I re-wrote a couple of projects ago...

selectdistinct questionID
from(
selectdistinct [table 1 primary key field] as questionID
from table1
where[a bunch of other stuff]
union
selectdistinct [table 2 primary key field] as questionID
from table2
where[a bunch of other stuff]
) as A
orderby questionID
...surely we can see the over kill, talk about doing more stuff than you really need to. The rewritten version ran about 6 times faster, and the developer was asking "how?". No magic I told him...you just need to be fully aware of what you're telling the db to do, and more importantly, don't tell it to do more than it needs to for a given purpose.

Edit: Changed "select distinct [table 2 primary key field] as as questionID" to "selectdistinct [table 2 primary key field] as questionID"

You're hired null. [<img src='/community/emoticons/emotion-1.gif' alt='' />] SQL Server is all about how to resolve to the smallest size and amount of data sets the quickest to arrive at a particular answer. You should ALWAYS think about that when programming SQL. It's set-based. The most efficient set is one that's small and well defined. Utilization of indexes, avoidance of loops, and quick identification and filtering of only necessary data will provide results of data faster than any other types of language out there. Getting object-oriented programmers to think this way though can be one of the biggest challenges for a DBA.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Sure thing, I came back to the thread and noticed a small typo, I noted it below the reply.

Btw, thx for the positive comments. I still consider myself relatively green to db development, but I've learned a ton since I've been out of college. Database has really turned into one of my primary interests, look fwd to sticking around and picking up a thing or two.

Hey Null Thanks a lot. <br />It's a very good clarification on UNION ALL. Now I need to check our existing SP#%92s for UNION ALL performance issue <img src='/community/emoticons/emotion-1.gif' alt='' />. <br /><br /><br /><h6>Surendra Kalekar</h6>