SQLTeam.com | Weblogs | Forums

Correct syntax using Union with nested query


#1

Using the query on it's own produces the correct result. using it together with union gives me a syntax error. Please guide me to the correct Solution.

 Select  Top 1 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount 

from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs
Union
Select Top 1 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs order by dcount desc


#2

When I parse the query, SQL (2016) doesn't show any error.


#3

My apologies. the correct query should be
Select Top 1 'v961' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'v961' and processtime <480
) T
group by BTB_Secs order by dcount desc
Union
Select Top 1 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs order by dcount desc

running this query results in a syntax error. running the query. running the query singularly produces the correct result. removing the order by clause before the Union produces the incorrect result. sorry..hope this makes sense.


#4

i am using sql 2012


#5

You have to put the ORDER BY in the first query in a derived table ("sub-SELECT"), like so:

select *
from (
Select Top (1) 'v961' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'v961' and processtime <480
) T
group by BTB_Secs order by dcount desc ) as derived
Union
Select Top (1) 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs order by dcount desc


#6

some progress thanks. running the the query give the correct result for the first query only.
Recipe BTB_Secs Dcount
v961 289 268
X331 null 0

The correct result for the second query should be
X331 201 53

Any further suggestions?


#7

Please provide sample DDL and DML

create table [Ban1_Data](ID int, BTB_Secs int, RecipeName varchar(50), 
processtime int, [Date] date);
create table BTB_Secs([Date] date, RecipeName varchar(50))

insert into Ban1_Data
select sample data here


``

#8

You don't have an ORDER BY on the second SELECT, so "TOP (1)" could be any row in the result.

If there should be only one row in the entire result set, then I have no idea why it comes back as NULL. I know absolutely nothing about any of your data ... how could I??


#9

Some sample data from thousands of entries

ID Date Recipename ProcessTime
117221 2015-03-13 08:54:16.043 X331 182
117222 2015-03-13 08:58:20.573 X331 178
117223 2015-03-13 09:02:01.370 X331 166
117224 2015-03-13 09:07:17.667 X331 176
117225 2015-03-13 09:10:58.447 X331 171
117226 2015-03-13 09:14:40.480 X331 179
117227 2015-03-13 09:18:58.777 X331 185
117228 2015-03-13 09:22:43.557 X331 175
117229 2015-03-13 09:26:11.823 X331 164
117230 2015-03-13 09:29:32.857 X331 163
117450 2015-03-16 13:21:24.423 V961 210
--- --- --- ---
117451 2015-03-16 13:25:34.720 V961 213
117452 2015-03-16 14:21:14.563 V961 231
117453 2015-03-16 14:25:37.077 V961 213
117454 2015-03-16 14:29:51.827 V961 214
117455 2015-03-16 14:34:08.077 V961 207
117456 2015-03-16 14:38:28.327 V961 212
117457 2015-03-16 14:42:51.597 V961 212
117458 2015-03-16 14:47:12.097 V961 212
117459 2015-03-16 14:51:32.847 V961 212

hope this help.


#10

the idea is to find the mode of each recipe grouped by the Batch to Batch times.


#11

Please post as proper dml

Select 'd', '2019-10-22'

Etc. Folks take time out of their busy schedule to help you. Help us help you

Or

INSERT INTO employees (first_name, last_name, fname) VALUES ('John', 'Capita', 'xcapit00');

#12

i am sorry. i am novice. i will inquire on dml and reply soonest.


#13

basically dml is sql data manipulation language

so in your case you need to provide us all of that data as follows

select 117221,	'2015-03-13 08:54:16.043',	'X331',	182 union
select 117222,	'2015-03-13 08:54:16.043',	'X331',	178

etc, the whole data set you pasted there.


#14

As both selects are almost identical, you could do:

select top(1) with ties
       recipename
      ,btb_secs
      ,dcount
  from (select recipename
              ,btb_secs
              ,count(btb_secs) as dcount
          from (select recipename
                      ,datediff(second
                               ,lag([date],1,null) over(partition by recipename
                                                            order by id
                                                       )
                               ,[date]
                               )
                       as btb_secs
                  from millroom.bizuser.ban1_data
                 where recipename in ('v961','X331')
                   and processtime<480
                ) as a
         group by recipename
                 ,btb_secs
       ) as a
 order by row_number() over(partition by recipename
                                order by dcount desc
                           )
;

#15

hi

i tried to solve this
its a very simple error

hope this helps
:slight_smile:
:slight_smile:

drop create data
drop table #abc 

create table #abc 
(ID	int,
Date1 datetime,	
Recipename varchar(100),
ProcessTime int  )
go 


insert into #abc select 117221,'2015-03-13 08:54:16.043','X331',182
insert into #abc select 117222,'2015-03-13 08:58:20.573','X331',178
insert into #abc select 117223,'2015-03-13 09:02:01.370','X331',166
insert into #abc select 117224,'2015-03-13 09:07:17.667','X331',176
insert into #abc select 117225,'2015-03-13 09:10:58.447','X331',171
insert into #abc select 117226,'2015-03-13 09:14:40.480','X331',179
insert into #abc select 117227,'2015-03-13 09:18:58.777','X331',185
insert into #abc select 117228,'2015-03-13 09:22:43.557','X331',175
insert into #abc select 117229,'2015-03-13 09:26:11.823','X331',164
insert into #abc select 117230,'2015-03-13 09:29:32.857','X331',163
insert into #abc select 117450,'2015-03-16 13:21:24.423','V961',210
insert into #abc select 117451,'2015-03-16 13:25:34.720','V961',213
insert into #abc select 117452,'2015-03-16 14:21:14.563','V961',231
insert into #abc select 117453,'2015-03-16 14:25:37.077','V961',213
insert into #abc select 117454,'2015-03-16 14:29:51.827','V961',214
insert into #abc select 117455,'2015-03-16 14:34:08.077','V961',207
insert into #abc select 117456,'2015-03-16 14:38:28.327','V961',212
insert into #abc select 117457,'2015-03-16 14:42:51.597','V961',212
insert into #abc select 117458,'2015-03-16 14:47:12.097','V961',212
insert into #abc select 117459,'2015-03-16 14:51:32.847','V961',212
go 

select * from #abc 
go
SQL ..
SELECT a.* 
FROM   (SELECT TOP 1 'v961'          AS Recipe, 
                     btb_secs, 
                     Count(btb_secs) AS DCount 
        FROM   (SELECT recipename, 
                       Datediff(ss, Lag([date1]) 
                                      OVER ( 
                               a         ORDER BY id), [date1])AS BTB_Secs 
                FROM   #abc 
                WHERE  recipename = 'v961' 
                       AND processtime < 480) T 
        GROUP  BY btb_secs 
        ORDER  BY dcount DESC 
        UNION ALL 
        SELECT TOP 1 'X331'          AS Recipe, 
                     btb_secs, 
                     Count(btb_secs) AS DCount 
        FROM   (SELECT recipename, 
                       Datediff(ss, Lag([date1]) 
                                      OVER ( 
                                        ORDER BY id), [date1])AS BTB_Secs 
                FROM   #abc 
                WHERE  recipename = 'X331' 
                       AND processtime < 480) T 
        GROUP  BY btb_secs 
        ORDER  BY dcount DESC) a

image