2

I have am attempting to randomly sample random entries for a one-week period. In order to ensure that I have a balance of weekday and weekend entries, I have sketched out 2 separate sql statements as follows:

select * FROM admin.acct_activity
where RANDOM() <=1
        --weekday
        and extract(day from page_hit_ts) Between 6 and 10
limit 500
UNION all

SELECT *
FROM admin.acct_activity
where RANDOM() <=1
        --weekend
        and extract(day from page_hit_ts) Between 11 and 12
limit 200
/* to-do
 1. limit results for each query
 2. order query results by date*/

error message is:

"all" (at char 121) expecting a keyword (State:42000, Native Code: 1B)

this is in Netezza SQL (NZSQL)

the limit statement in the first select statement seems to cause the error. Any tips?

mc110
  • 2,825
  • 5
  • 20
  • 21
Mike
  • 397
  • 5
  • 19
  • If you have an error and dont post it, it really makes my crystal ball work more than it should. :) Please post all the info you have about your problem. – crthompson Jun 18 '14 at 14:28
  • 1
    @Goon10 FYI, `LIMIT` is valid syntax in Netezza! – Satwik Nadkarny Jun 18 '14 at 14:31
  • @SatwikNadkarny : all right, good to know. – Goon10 Jun 18 '14 at 14:34
  • 1
    Assuming inividually the queries return what you want, you may need to wrap them in ()'s the UNION may be attempted prior to the limit and then the limt is perceived as a syntax error. http://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query While not the same, databases perform similarly – xQbert Jun 18 '14 at 14:35
  • @xQbert it was this exactly, thanks – Mike Jun 18 '14 at 15:06

2 Answers2

5

I think you can do this with parentheses:

(select *
 FROM admin.acct_activity
 where RANDOM() <=1 and extract(day from page_hit_ts) Between 6 and 10
 limit 500
)
UNION all
(SELECT *
 FROM admin.acct_activity
 where RANDOM() <=1 and extract(day from page_hit_ts) Between 11 and 12
 limit 200
)

If not, you can definitely do it with a subquery:

select t.*
from ((select *
       FROM admin.acct_activity
       where RANDOM() <=1 and extract(day from page_hit_ts) Between 6 and 10
       limit 500
      )
     UNION all
     (SELECT *
      FROM admin.acct_activity
      where RANDOM() <=1 and extract(day from page_hit_ts) Between 11 and 12
      limit 200
     )
    ) t
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can only set one LIMIT statement per query. You should put each limited query into a temp table, then UNION ALL the temp tables.

mrtig
  • 2,217
  • 16
  • 26