How to SELECT TOP 1 with UNION and ORDER BY

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

General SQL Server Forums

New to SQL Server Programming

How to SELECT TOP 1 with UNION and ORDER BY

Author

Topic

dayol33
Starting Member

2 Posts

Posted - 2012-01-18 : 17:28:58

Sorry everyone... I'm the new guy to the forum. Thanks in advance for all your help.

I'm trying to write a query that UNIONs 2 queries together. I'd like the query ORDERED BY the results of my first query followed by the results from my second query, then by another criteria. After the appropriate order is set, I only want the query to return the TOP 1. This is what I have so far, and for some reason, its returning mulitple rows instead of just the top 1. Please help.

SELECT TOP 1 *
FROM (SELECT TABLE.FIELD1, 1 AS OrderBy
FROM TABLE
WHERE TABLE.FIELD2 = "whatever"

UNION

SELECT TABLE.FIELD1, 2 AS OrderBy
FROM TABLE
WHERE TABLE.FIELD2 = "whatever"
AND TABLE.FILED3 = "something") AS CallOrder
ORDER BY OrderBy, FIELD3;

Any help is appreciated.

- FRUSTRATED SQL newbie...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 17:51:31

Hello dayol33,

Perhaps using a derived table will help you here. Something like the following form:

SELECT TOP 1 d.<your fields>
FROM
(
		SELECT TOP 1 *
		FROM (SELECT TABLE.FIELD1, 1 AS OrderBy
		FROM TABLE
		WHERE TABLE.FIELD2 = "whatever"

		UNION

		SELECT TABLE.FIELD1, 2 AS OrderBy
		FROM TABLE
		WHERE TABLE.FIELD2 = "whatever"
		AND TABLE.FILED3 = "something") AS CallOrder
		ORDER BY OrderBy, FIELD3;
) d
ORDER BY d.orderBy, <other fields>

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-18 : 17:52:14

It's hard to tell what the issue is. I can look at the sample query you posted and see it wouldn't compile. If you posted the real query we might be able to help, but based on your sample query you seem to be on a path that should work.

Here are some links that might help when posting your question(s):
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dayol33
Starting Member

2 Posts

Posted - 2012-01-19 : 12:19:59

Thank you ehorn and Lamprey for your responses.
EHORN- question. Why did you use the TOP 1 in the first subquery but not the second?

Yesterday, I realizedd that the query that I wrote isn't returning the TOP record, but returning the TOP records that match the first ORDER BY criteria. So if I only "ORDERED BY" my data by OrderBy, it returned all records that had the variable OrderBy = 1, which are all the results from my first subquery. If I add another criteria in my ORDER BY clause, it returns all records that satisify both ORDER BY criterias. For example, if I add "Priority" as the 2nd criteria, it returns all records that have OrderBy = 1 and Priority = 1.

After realizing this, I decided to just ORDER BY the unique ID so that I would only get one record returned... which theoretically works... but I'm curious as to how to properly code my query to return only 1 record without the "shortcut".

AGAIN, that you both for your prompt responses.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 12:24:43

quote:
Originally posted by dayol33

Thank you ehorn and Lamprey for your responses.
EHORN- question. Why did you use the TOP 1 in the first subquery but not the second?

Hello dayol33,

I likely constructed it that way because I was not clear about your need. lol...

The idea is when you have to combine results and them apply sorting, it is often useful to use a derived table structure to force additional sorting over these combined results. This was more what I was attempting to demonstrate.

FWIW, HTH.

Have a nice day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources