0

There's probably a simple solution to this. However my question is how do i go about selecting say 100 or 1000 distinct avclassfamily values in the following query?

Ideally there would be a command that'd be something like 'select distinct ON (1000) (avclassfamily)' but there isn't. 

    cursor.execute("select count(*),date_trunc( 'year', first_seen ) from (select DISTINCT ON (avclassfamily) * from malwarehashesandstrings) as p where first_seen is not null and behaviouralbinary is true and origindataset != 'MalRec' and origindataset != 'Ember Benign' and origindataset is not null group by date_trunc( 'year', first_seen );")

Please let me know if you have a solution or if there's anytthing i can clarify

Edit: As requested, a simpler version of the query:

     select DISTINCT (avclassfamily) from malwarehashesandstrings;

But say if it was possible to select 100 distinct values there

dipl0
  • 1,017
  • 2
  • 13
  • 36
  • Please add sample data, as it is not obvious (at least, not to me) what your query is trying to do here. Note that you _can't_ use `DISTINCT ON` without an `ORDER BY` clause. – Tim Biegeleisen Jun 03 '20 at 05:45
  • 1
    @TimBiegeleisen: well, you _can_ do that (it won't result in a runtime error), but it doesn't make sense. –  Jun 03 '20 at 05:49
  • @a_horse_with_no_name Didn't know that, keep learning new things about Postgres from you (last tidbit was that `SELECT` followed by table name is valid inside exists clause) `:-)` – Tim Biegeleisen Jun 03 '20 at 05:51

0 Answers0