Re: UNION query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 07/09/04 

Date: Fri, 09 Jul 2004 22:20:00 +0200

ORDER BY is a cursor operation. This means it can apply only once, and
is applied to the result of the statement.

So in your case, the first ORDER BY clause is invalid at that position.

Your second ORDER BY clause is not useful, and is also at an invalid
position.

And finally, you can use UNION ALL instead of UNION.

You could rewrite your statement to something like:

  SELECT T1.CedGroup, T1.OPrem3
  FROM (
    SELECT TOP 5 .. AS CedGroup, .. AS OPrem3
    FROM ..
    ORDER BY ..
  ) AS T1
  
  UNION ALL
  
  SELECT 'Other Cos', SUM(ProgramSummary.OPrem3)
  FROM ..
  GROUP BY ProgramSummary.CedGroup
  
  ORDER BY OPrem3

Hope this helps,
Gert-Jan

ndatt wrote:
>
> I'm trying to do a UNION qry but am getting a syntax error on the union. I'm not sure why but seems to be something due to the order by. I'm trying to union a list of the top 5 companies and their premium with the subtotal premium of all the other companies. Any ideas would be helpful.
>
> SELECT TOP 5 ProgramSummary.CedGroup, SUM(ProgramSummary.OPrem3) AS PremSum
> FROM ProgramSummary LEFT OUTER JOIN
> BSegs ON ProgramSummary.BSegID = BSegs.BSegID
> WHERE (ProgramSummary.StatID = 2) AND (ProgramSummary.DtEff <= GETDATE()) AND (ProgramSummary.DtExp >= GETDATE()) AND
> (BSegs.Division = 1) AND (BSegs.BranchKey = 10)
> GROUP BY ProgramSummary.CedGroup
> ORDER BY SUM(ProgramSummary.OPrem3) DESC
>
>
> UNION SELECT 'Other Cos', SUM(ProgramSummary.OPrem3)
> FROM ProgramSummary LEFT OUTER JOIN
> BSegs ON ProgramSummary.BSegID = BSegs.BSegID
> WHERE (ProgramSummary.StatID = 2) AND (ProgramSummary.DtEff <= GETDATE()) AND (ProgramSummary.DtExp >= GETDATE()) AND (BSegs.Division = 1) AND
> (BSegs.BranchKey = 10) AND ProgramSummary.CedGroup Not In
> (SELECT TOP 5 ProgramSummary.CedGroup
> FROM ProgramSummary LEFT OUTER JOIN
> BSegs ON ProgramSummary.BSegID = BSegs.BSegID
> WHERE (ProgramSummary.StatID = 2) AND (ProgramSummary.DtEff <= GETDATE()) AND (ProgramSummary.DtExp >= GETDATE()) AND
> (BSegs.Division = 1) AND (BSegs.BranchKey = 10)
> GROUP BY ProgramSummary.CedGroup
> ORDER BY SUM(ProgramSummary.OPrem3) DESC)
> ORDER BY SUM(ProgramSummary.OPrem3) 

-- 
(Please reply only to the newsgroup)

Relevant Pages

  • SQL Injection: Issue with UNION SELECT ALL
    ... using a UNION SELECT ALL statement. ... an equal number of expressions in their target lists. ... Column 'a.id' is invalid in the select list because it is not contained ... in an aggregate function and there is no GROUP BY clause. ...
    (Pen-Test)
  • Re: The Sands of Time Do Not Lie...
    ... In something like the Union, ... Unless you can show me a clause that allows it, ... crafted it believed that powers not expressly granted to the federal ... argument for not having a bill of rights. ...
    (talk.origins)
  • Re: Sorting Order by Table?
    ... | some PL/SQL code and opened via REF CURSOR. ... I want to add another table to the FROM clause and another set ... | of criteria to the WHERE clause. ... select statement with some sort of UNION and weird sorting. ...
    (comp.databases.oracle.misc)
  • Re: Sorting Order by Table?
    ... I want to add another table to the FROM clause and another set ... | of criteria to the WHERE clause. ... select statement with some sort of UNION and weird sorting. ...
    (comp.databases.oracle.misc)
  • Re: Re: The Sands of Time Do Not Lie...
    ... In something like the Union, ... Unless you can show me a clause that allows it, ... crafted it believed that powers not expressly granted to the federal ... argument for not having a bill of rights. ...
    (talk.origins)