UNION ALL/TOP x Issue

• rruest

  SSC Enthusiast

  Points: 142

  More actions

  January 7, 2009 at 11:45 am

  #126823

  I have a few SELECTS joined by UNION ALL... pseudo code:

  SELECT somedata FROM Table1

  UNION ALL

  SELECT somedata FROM Table2

  The query works perfectly. The user requires me to limit the results of the 2nd SELECT. When I try to do this, I get results from the first SELECT, not the second. I've tried..

  SELECT somedata FROM Table1

  UNION ALL

  SELECT TOP 5 * FROM

  (SELECT somedata FROM Table2 ORDER BY TransDate DESC)

  as well as

  SELECT somedata FROM Table1

  UNION ALL

  SELECT * FROM

  (SELECT TOP 5 somedata FROM Table2 ORDER BY TransDate DESC)

  still nothing. Any ideas on how to limit just one of the selects? The SQL generates no error, but does not return any results. Each select returns results on its own. Help! :w00t:

  Thanks!

  Ray

• vick.ram79

  SSCommitted

  Points: 1637

  More actions

  January 7, 2009 at 12:39 pm

  #922828

  Is this 2005 sql ??

  when i tried your query:

  SELECT somedata FROM Table1

  UNION ALL

  SELECT TOP 5 * FROM

  (SELECT somedata FROM Table2 ORDER BY TransDate DESC)

  I got an error but I simply gave an alias to the subquery and it worked fine.

  SELECT somedata FROM Table1

  UNION ALL

  SELECT * FROM

  (SELECT TOP 5 somedata FROM Table2 ORDER BY TransDate DESC) SomeStupidAlias

  How To Post[/url]

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 7, 2009 at 12:42 pm

  #922832

  rruest (1/7/2009)

  ---

  I have a few SELECTS joined by UNION ALL... pseudo code:

  SELECT somedata FROM Table1

  UNION ALL

  SELECT somedata FROM Table2

  The query works perfectly. The user requires me to limit the results of the 2nd SELECT. When I try to do this, I get results from the first SELECT, not the second. I've tried..

  SELECT somedata FROM Table1

  UNION ALL

  SELECT TOP 5 * FROM

  (SELECT somedata FROM Table2 ORDER BY TransDate DESC)

  as well as

  SELECT somedata FROM Table1

  UNION ALL

  SELECT * FROM

  (SELECT TOP 5 somedata FROM Table2 ORDER BY TransDate DESC)

  still nothing. Any ideas on how to limit just one of the selects? The SQL generates no error, but does not return any results. Each select returns results on its own. Help! :w00t:

  Thanks!

  Ray

  This is by Design!

  FROM BOL:

  When UNION is used, the individual SELECT statements cannot have their own ORDER BY or COMPUTE clauses. There can be only one ORDER BY or COMPUTE clause after the last SELECT statement; it is applied to the final, combined result set. GROUP BY and HAVING can be specified only in the individual SELECT statements.

  
  * Noel

• rruest

  SSC Enthusiast

  Points: 142

  More actions

  January 7, 2009 at 12:55 pm

  #922841

  Vic... yes, SQL 2005. Added an alias, no go.

  Noel... bad design! 😀 I removed the ORDER BY and still get no results. Even just added the TOP keyword returns no results, so that is the culprit.

  Any suggestions/ideas for getting this done?

• vick.ram79

  SSCommitted

  Points: 1637

  More actions

  January 7, 2009 at 12:58 pm

  #922848

  Since I am obviously not able to see/recreate the problem, could the original poster confirm if using CTE

  solves the problem. Like:

  with myCTE(

  somedata)

  as(SELECT TOP 5 somedata FROM Table2 order by TransDate DESC)

  select somedata FROM Table1

  UNION ALL

  SELECT * FROM myCTE

  How To Post[/url]

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 7, 2009 at 2:27 pm

  #922923

  vick.ram79 (1/7/2009)

  ---

  Since I am obviously not able to see/recreate the problem, could the original poster confirm if using CTE

  solves the problem. Like:

  with myCTE(

  somedata)

  as(SELECT TOP 5 somedata FROM Table2 order by TransDate DESC)

  select somedata FROM Table1

  UNION ALL

  SELECT * FROM myCTE

  I can make it work like vick.ram79 suggested though:

  select b.* from

  (select top (5) RowNumber from Table_1 Order by RowNumber) b

  union all

  select k.* from

  (select top (5) RowNumber from Table_2 Order by RowNumber desc) k

  
  * Noel

• gyessql

  Ten Centuries

  Points: 1097

  More actions

  January 8, 2009 at 7:50 am

  #923334

  This could also serve your purpose:

  DECLARE @maxrows INT

  SELECT @maxrows = COUNT(1) FROM Table1 -- or SET @maxrows = 10;

  SELECT TOP (@maxrows) col1 FROM Table1

  UNION ALL

  SELECT TOP (2) col1 FROM Table2

• rruest

  SSC Enthusiast

  Points: 142

  More actions

  January 9, 2009 at 9:22 am

  #924305

  I have tried variations of each and was able to get is done by partitioning. Every other method produced the same results. Was very surprised to see CTE exhibit the same behavior. Thanks for all the helpful suggestions! 🙂 Not sure who suggested the PARTITION, it looks like you deleted the post. If you read this later...thanks!

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 9, 2009 at 9:28 am

  #924315

  The other solution would be to insert the desired rows into temp tables and then Union the temp tables.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• rruest

  SSC Enthusiast

  Points: 142

  More actions

  January 9, 2009 at 9:39 am

  #924331

  Good point GSquared. Sometimes it's easier to go down that path.