[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Error 3351 problem

The ORDER BY expression includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression. (Error 3351)
I created the Union query
the every part of the query is working fine
it does retrieve the right number
As soos as i run the union query it gives me the error above
what seems to be a problem
0
rfedorov
Asked:
rfedorov
  • 12
  • 8
1 Solution
 
rfedorovAuthor Commented:
or may I use the following instead of Union query
is this possible to run a set of queries?
select * from table A
select * from table B
select * from (select * from table A)
select * from (select * from table B)
0
 
Rey Obrero (Capricorn1)Commented:

select a.* From
(
select * from [Table A]
union All
Select * from [table b]
) as a
0
 
rfedorovAuthor Commented:
thank you, but what seems to be a problem with my union query
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
i don't see your union query.
0
 
rfedorovAuthor Commented:
here we go
SELECT 
          TOP 1 [tableA].[Date received 1] as [Latest Date], 
          count ([tableA].[Date received 1]) as [Count],
          Last ((select count(*) from [tableA])) as Total
FROM 
          [tableA]
group by  [tableA].[Date received 1] 
order by   [tableA].[Date received 1]  desc ;

UNION 


SELECT 
          TOP 1 [tableB].[Date received] as [Latest Date],
          Count ([tableB].[Date received]) as [Count],
          Last ((select count(*) from [tableB]))  as Total
FROM 
          [tableB]
group by  [tableB].[Date received]
order by   [tableB].[Date received] desc ;

Open in new window

0
 
rfedorovAuthor Commented:
1 part of union return ( if run separately)
Latest Date      Count      Total
3/22/2010      29      377
2 part of union return ( if run separately)
Latest Date      Count      Total
3/22/2010      1      10
0
 
Rey Obrero (Capricorn1)Commented:
SELECT  
          TOP 1 [tableA].[Date received 1] as [Latest Date],  
          count ([tableA].[Date received 1]) as [Count],
          Last ((select count(*) from [tableA])) as Total
FROM  
          [tableA]
group by  [tableA].[Date received 1]  

UNION  
 
SELECT  
          TOP 1 [tableB].[Date received] as [Latest Date],
          Count ([tableB].[Date received]) as [Count],
          Last ((select count(*) from [tableB]))  as Total
FROM  
          [tableB]
group by  [tableB].[Date received]
order by 1  desc
0
 
rfedorovAuthor Commented:

it gave the results :
which is not right, should be both 3/22/2010 or may be somthing else i am doing wrong
Latest Date      Count      Total
3/22/2010      29      377
3/12/2010      1      10
0
 
Rey Obrero (Capricorn1)Commented:
save the first query as query1, and the second as query2,
then create a union query


SELECT * from query1
union
select * from query2
0
 
rfedorovAuthor Commented:
i do that right now, but i thought i could do everything at once...
what seems to be a problem
0
 
rfedorovAuthor Commented:
HOw about if you look at this
First i run the query1

then I run the final query2
how to combine them?
my query1

SELECT 
"1" as [Error],
TableA.[Date received 1], 
count(TableA.[Date received 1] ) as [count]
FROM
TableA
group by TableA.[Date received 1]

UNION 

SELECT 
"2" as [Error],
TableB.[Date received],
count(TableB.[Date received])  as [count]
FROM 
TableB
group by TableB.[Date received];





Query2
SELECT 
query1.Error, 
Last(query1.[Date received 1]) AS [Last Date], 
Last(query1.count) AS LastOfcount, Sum(query1.count) AS SumOfcount1
FROM query1
GROUP BY query1.Error;

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you can try and see the result
0
 
rfedorovAuthor Commented:
no, i want to combine them in one query, it does not work for me
0
 
Rey Obrero (Capricorn1)Commented:
is there a problem doing this

save the first query as query1, and the second as query2,
then create a union query


SELECT * from query1
union
select * from query2
0
 
rfedorovAuthor Commented:
i am doing that already , but i want only one query needs to be run
0
 
Rey Obrero (Capricorn1)Commented:
sorry, but i think, that is the only way you can do that to satisfy the order by clause in both queries
0
 
rfedorovAuthor Commented:
Ok, you are saying that it is not possible to combine two queries in the one?
0
 
rfedorovAuthor Commented:
May be you do not understand what i want, i want to combine two queries
from 03/23/10 01:37 PM, ID: 28385223          in one.
0
 
Rey Obrero (Capricorn1)Commented:
not in this case..  
0
 
rfedorovAuthor Commented:
Unfortunately, it it working and i did combined two queries into one
It took me a while, but i want to thank you for the "developing"  of my mind
All points are yours, the only thing i would like to ask you :
is this possible to create macro and run at any time and where to start?
SELECT  
A.Error,  
Last(A.[Date received 1]) AS [Last Date],  
Last(A.count) AS LastOfcount, 
Sum(A.count) AS SumOfcount1 
FROM 
(
SELECT  
"1" as [Error], 
TableA.[Date received 1],  
count(TableA.[Date received 1] ) as [count] 
FROM 
TableA 
group by TableA.[Date received 1] 
 
UNION  
 
SELECT  
"2" as [Error], 
TableB.[Date received], 
count(TableB.[Date received])  as [count] 
FROM  
TableB 
group by TableB.[Date received]
) A 
 
GROUP BY A.Error;

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now