SQL UNION HELP

SQL UNION HELP

Post by rick » Wed, 18 Jun 2003 14:44:35



I want to do something like this:

SELECT TOP 100 PERCENT (A),COUNT(*)AS A1
FROM TBLa
GROUP BY A
ORDER BY COUNT (*) DESC

UNION

SELECT TOP 100 PERCENT (B),COUNT(*)AS B1
FROM TBLb
GROUP BY B
ORDER BY COUNT (*) DESC

But SQL wont do it.  Any ideas on how to run this?

Thanks so much
Rick

 
 
 

SQL UNION HELP

Post by David Porta » Wed, 18 Jun 2003 15:34:03


ORDER BY applies to the whole UNION and so can only be used once in a UNION.
Try:

SELECT a AS x, COUNT(*) AS cnt
 FROM tbla
 GROUP BY a
 UNION
SELECT b, COUNT(*)
 FROM tblb
 GROUP BY b
 ORDER BY COUNT (*) DESC

or, if you want the A values sorted before the Bs:

SELECT x, cnt
 FROM
 (SELECT 1 AS u, a AS x, COUNT(*) AS cnt
  FROM tbla
  GROUP BY a
  UNION
 SELECT 2 AS u, b, COUNT(*)
  FROM tblb
  GROUP BY b) AS T
 ORDER BY u, cnt DESC

Note that this second query may produce a slightly different result since
the UNION will not remove duplicate rows between the two tables.

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> I want to do something like this:

> SELECT TOP 100 PERCENT (A),COUNT(*)AS A1
> FROM TBLa
> GROUP BY A
> ORDER BY COUNT (*) DESC

> UNION

> SELECT TOP 100 PERCENT (B),COUNT(*)AS B1
> FROM TBLb
> GROUP BY B
> ORDER BY COUNT (*) DESC

> But SQL wont do it.  Any ideas on how to run this?

> Thanks so much
> Rick


 
 
 

SQL UNION HELP

Post by Matthew Band » Wed, 18 Jun 2003 15:38:51


I haven't tried this to see what else might be wrong yet,
but when you are using union, the column names from the
first query will be used as the column names for your
result set.  You cannot use column aliases in the
subsequent queries. You can try this:

SELECT TOP 100 PERCENT (A),COUNT(*)AS A1
FROM TBLa
GROUP BY A
ORDER BY COUNT (*) DESC

UNION

SELECT TOP 100 PERCENT (B),COUNT(*)
FROM TBLb
GROUP BY B
ORDER BY COUNT (*) DESC

The top 100 percent should allow you to get around the no-
order by clause, but I'm not really sure if your results
will be the way you want them.

Matthew Bando

Quote:>-----Original Message-----
>I want to do something like this:

>SELECT TOP 100 PERCENT (A),COUNT(*)AS A1
>FROM TBLa
>GROUP BY A
>ORDER BY COUNT (*) DESC

>UNION

>SELECT TOP 100 PERCENT (B),COUNT(*)AS B1
>FROM TBLb
>GROUP BY B
>ORDER BY COUNT (*) DESC

>But SQL wont do it.  Any ideas on how to run this?

>Thanks so much
>Rick

>.

 
 
 

SQL UNION HELP

Post by David Porta » Wed, 18 Jun 2003 18:12:06


Only one ORDER BY clause is allowed in a UNION. See my earlier reply.

--
David Portas
------------
Please reply only to the newsgroup
--

 
 
 

1. Need help with sql script using UNION

I have a table that contains all the files that are on my
machine (inst_file) and another table (package_desc) that
describes software packages (e.g., Microsoft Word 7.0 contains
word.exe, word.dll, word.txt, I'll have three rows in this
table for package Microsoft Word 7.0).  Now, my goal is to
find out if this package exist on my machine by looking at
these two tables.

1- I have a view that joins these two tables and matches
   all the files that I have installed on the machine with
   all the files that exist in my package. (inst_file_view)
2- I now need to find if the package "Microsoft Word 7.0" is
   installed on my machine.  In order to do so, I create two
   more views (inst_comp_view and find_sw_view) that are described
   below at the end of the message. Col1, Col2 are attributes
   like file size, file name, package name, package version, that
   are used in the where clauses.
   I've been using the above views in Oracle and it works fine, but
   when I try to create view "inst_comp_view" in MS SQL I get the
   following errors:

   Msg 209, Level 16, State 1
   Ambiguous column name col1
   Msg 209, Level 16, State 1
   Ambiguous column name col2
      ...
   Msg 206, Level 16, State1
   Operand type clash: UNKNOWN TOKEN is incompatible with varchar
   Msg 206, Level 16, State1
   Operand type clash: UNKNOWN TOKEN is incompatible with int

I decomposed inst_comp_view even further and created a view that is
a union of two views (one view for each select statemnt).  This works
just fine and gives me the right result.  However, when I get to run
"select * from find_sw_view" I get:

The query and the views in it exceed the limit of 16 tables.

I'll appreciate any help.  I'd like to know why I can't create inst_comp_view
and also please let me know if you have a better approach to solve this problem.
Of course, this could easily be solved using a procedural language like PL/SQL
or T-SQL, but we're trying to see if we can solve it using just standard SQL.

Thanks in advance,
Niloufar

(inst_comp_view)
create view inst_comp_view as
select col1, col2, ..., 'T' FOUND from package_desc, inst_file_view
union
select col1, col2, ..., 'F' FOUND from package_desc, inst_file_view
where not exists (select 'X' from inst_file_view
                   where package_desc.col1 = inst_file_view.col1
                     and package_desc.col2 = inst_fiel_view.col2)

(find_sw_view as)

select distinct col1, col2, col3,... from inst_comp_view icv
where not exists (select 'X' from inst_comp_view icv1
                   where icv1.col1 = icv.col1
                     and icv1.col2 = icv.col2)

2. FTS < HTML > wordbreaker

3. SQL UNION Beginners Help

4. 12V portable electronic Cooler/warmers

5. PL/SQL UNION ERROR : PLEASE HELP

6. Relating 2 Databases and List Boxes

7. Help...SQL UNION Error !!!

8. Compare 2 db?

9. help with UNION SQL

10. sql query (UNION) help!!! Thanks

11. a SQL Statement/Union Help

12. HELP: Unions in Delphi SQL

13. UNION, UNION ALL