How to implement a top n filter in Monetdb?

Question

I want to do a top 10 filter like this:

SELECT t0."A" AS d0,
  t0."B" AS d1,
  t0."C" AS d2,
  t0."D" AS d3,
  SUM(t0."SA") AS m0,
  SUM(t0."SB") AS m1
FROM "mock_table_1" AS t0
INNER JOIN (                  //the top 10 filter begin here
  SELECT t0."D" AS fd,
    SUM(t0."SD") AS top
  FROM "mock_table_1" AS t0
  GROUP BY t0."D"
  ORDER BY top ASC
  LIMIT 10
) AS p0u1
ON (t0."D" = p0u1.fd)         //the top 10 filter end here
GROUP BY t0."A",
  t0."B",
  t0."C",
  t0."D"
HAVING (SUM(t0."X") <= 100000000)
LIMIT 100

but this doesn't work since order-by in subquery not avaliable in Monetdb?

So, what should I do to implement this top n filter ?

The simplified SQL example:

SELECT t0."A" AS d0,
  SUM(t0."SA") AS m0
FROM "mock_table_1" AS t0
INNER JOIN (                    //the top 10 filter begin here
  SELECT t0."D" AS fd,
    SUM(t0."SD") AS top_cond
  FROM "mock_table_1" AS t0
  GROUP BY t0."D"
  ORDER BY top_cond ASC
  LIMIT 10
) AS top_filter
ON (t0."D" = top_filter.fd)     //the top 10 filter end here
GROUP BY t0."A"
LIMIT 100

What I want to do here is to query A and SUM(SA) from "mock_table_1" where D is in the top-10-D-members, and the top-10-D-members means the members of field D which has the smallist SUM(SD)

start here? http://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or-match-by-the-nearest-value-without-a-top-or-lim — Anthony Damico, Jul 25 '16 at 00:08
I can give you a pretty simple answer, but first - please use a _minimal_ example, i.e. the simplest table and query you can think of for which you can formualte a "top n filter" query. — einpoklum, Dec 12 '16 at 10:56
@einpoklum Sorry, I gives this query because I thought it is simple enough to make sence, I can simplify it if you want. — luochen1990, Dec 14 '16 at 09:17
@luochen1990: Please do (but don't remove the complex one since someone already gave an answer based on it). — einpoklum, Dec 14 '16 at 09:34
@luochen1990: I think you misunderstood what I meant. I didn't mean a simpler _query_ but rather a simple _example_ of what you _want_... something like "I want to get the top 10 highest elements from column A in a table T." — einpoklum, Dec 14 '16 at 09:53
@einpoklum What I want to do here is to query A and SUM(SA) from "mock_table_1" where D is in the top-10-D-members, and the top-10-D-members means the members of field D which has the smallist SUM(SD) — luochen1990, Jan 10 '17 at 13:05

score 0 · Answer 1 · answered Aug 08 '16 at 20:55

So create function that does this that returns an unencumbered(by sub query restrictions) table

CREATE FUNCTION my_cheating_function() 
   RETURNS TABLE (fd [data type for fd], top [data type for top])
   RETURN TABLE (
        SELECT 
           t0."D" AS fd,
           SUM(t0."SD") AS top
        FROM 
           "mock_table_1" AS t0
        GROUP BY 
           t0."D"
        ORDER BY 
           top ASC
        LIMIT 10
   );

Then modify your original query:

  SELECT t0."A" AS d0,
   t0."B" AS d1,
   t0."C" AS d2,
   t0."D" AS d3,
   SUM(t0."SA") AS m0,
   SUM(t0."SB") AS m1
  FROM 
   "mock_table_1" AS t0
  INNER JOIN 
   my_cheating_function() AS p0u1
  ON 
   (t0."D" = p0u1.fd)        
  GROUP BY 
   t0."A",
   t0."B",
   t0."C",
   t0."D"
  HAVING (SUM(t0."X") <= 100000000)
  LIMIT 100;

This can most probably also be done by creating a view on that sub query and joining the the view in a similar fashion as above. FYI to create that view do the following:

CREATE VIEW my_cheating_view AS
    SELECT 
       t0."D" AS fd,
       SUM(t0."SD") AS top
    FROM 
       "mock_table_1" AS t0
    GROUP BY 
       t0."D"
    ORDER BY 
       top ASC
    LIMIT 10;

How to implement a top n filter in Monetdb?

1 Answers1