We help IT Professionals succeed at work.

Top 10 & Last 5

Kongta
Kongta used Ask the Experts™
on
I would like to show the Top10 & Last 5 of a table in one query. I tried

SELECT TOP 5 dbo_tblStatistikTmp_sb_Index.IQ, dbo_tblStatistikTmp_sb_Index.Index, dbo_tblStatistikTmp_sb_Index.Anzeige
FROM dbo_tblStatistikTmp_sb_Index
WHERE (((dbo_tblStatistikTmp_sb_Index.Technik_Anzahl)>50))
ORDER BY dbo_tblStatistikTmp_sb_Index.IQ;
 UNION
SELECT TOP 10 dbo_tblStatistikTmp_sb_Index.IQ, dbo_tblStatistikTmp_sb_Index.Index, dbo_tblStatistikTmp_sb_Index.Anzeige
FROM dbo_tblStatistikTmp_sb_Index
WHERE (((dbo_tblStatistikTmp_sb_Index.Technik_Anzahl)>50))
ORDER BY dbo_tblStatistikTmp_sb_Index.IQ DESC;

which gives me back the wrong results. Each query alone brings back the correct results, but in the UNION it doesn't work. Any idea why?
thx
Kongta
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Most Valuable Expert 2012
Top Expert 2013
Commented:
As far as I know, you can only have one ORDER BY clause in a UNION query.  You may be able to pull it off by hadling the two parts seperately as subqueries, though. Give this a try:

SELECT q1.IQ, q1.Index, q1.Anzeige
FROM
(
SELECT TOP 5 dbo_tblStatistikTmp_sb_Index.IQ, dbo_tblStatistikTmp_sb_Index.Index, dbo_tblStatistikTmp_sb_Index.Anzeige
FROM dbo_tblStatistikTmp_sb_Index
WHERE (((dbo_tblStatistikTmp_sb_Index.Technik_Anzahl)>50))
ORDER BY dbo_tblStatistikTmp_sb_Index.IQ) as q1
 UNION 
SELECT q2.IQ, q2.Index, q2.Anzeige
FROM
(
SELECT TOP 10 dbo_tblStatistikTmp_sb_Index.IQ, dbo_tblStatistikTmp_sb_Index.Index, dbo_tblStatistikTmp_sb_Index.Anzeige
FROM dbo_tblStatistikTmp_sb_Index
WHERE (((dbo_tblStatistikTmp_sb_Index.Technik_Anzahl)>50))
ORDER BY dbo_tblStatistikTmp_sb_Index.IQ DESC) AS q2

Open in new window

Kongta

Author

Commented:
perfect, have many thanks for the prompt answer
rgds
Kongta
mbizzzup
Most Valuable Expert 2012
Top Expert 2013

Commented:
If that does not work, try saving the two parts of the UNION query seperately and writing your UNION query like this:

SELECT IQ,  Index, Anzeige
FROM qryTop5
UNION
SELECT IQ,  Index, Anzeige
FROM qryLast10

Open in new window

mbizzzup
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad that worked out :-)