Combining two similar SQL queries
If I understand correctly, the query does a count in greatest-n-per-row type of query where n takes 2 values (40 and 100). 

This can probably be solved better by rewriting the subquery. The easier to write might be to do a join of two queries:

SELECT
    a.motif AS cntA, b.motif AS cntB, a.crossvalnum
FROM
    (query with limit = 40) AS a
  JOIN
    (query with limit = 100) AS b
      ON b.crossvalnum = a.crossvalnum ;


Here is another way by moving the counts inside the subquery:

SELECT   MIN(motif) AS cntA,
         MAX(motif) AS cntB,
         crossvalnum
FROM     (SELECT    ROW_NUMBER() OVER 
                        ( PARTITION BY crossvalnum 
                          ORDER BY crossvalnum, pvalue DESC, margstat
                        ) AS r,
                    COUNT(*) OVER 
                        ( PARTITION BY crossvalnum 
                        ) AS c,
                    COUNT(seqindex) OVER 
                        ( PARTITION BY crossvalnum 
                          ORDER BY crossvalnum, pvalue DESC, margstat
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        ) AS motif,
                    crossval.crossvalnum
         FROM       data
                    INNER JOIN datasubgroup
                    ON         data.datasubgroup_id=datasubgroup.id
                    INNER JOIN crossval
                    ON         datasubgroup.crossval_id=crossval.id
         WHERE      data.seqindex                      =ANY(crossval.sample)
         OR         data.seqindex                  IS NULL
         )
         AS q 
WHERE    r IN (LEAST(c, @limitA), LEAST(c, @limitB)) 
GROUP BY crossvalnum ;