OHDSI Home | Forums | Wiki | Github

Limiting SQL query results

Developers
#1

Greetings,

I need to limit the results for a templated SQL query and was wondering what are other people doing for this? SQL render does not translate LIMIT which works in Postgre and SQL Server, but of course not in Oracle. Currently I am opting for a nasty hard-coded ‘oracle’ only version of my query, but would like to see if there is a nicer alternative.

Thanks!

#2

ms SQL server uses SELECT TOP 10 *, does that work?

“Juan M. Banda” notifications@ohdsi.org wrote:

    Juan_Banda<http://forums.ohdsi.org/users/juan_banda>

October 9

Greetings,

I need to limit the results for a templated SQL query and was wondering what are other people doing for this? SQL render does not translate LIMIT which works in Postgre and SQL Server, but of course not in Oracle. Currently I am opting for a nasty hard-coded ‘oracle’ only version of my query, but would like to see if there is a nicer alternative.

Thanks!

#3

This works nicely with Postgre and MS SQL, but SQLrender does not support it and translates it as-is for Oracle, which is invalid syntax.

Thanks!

#4

@schuemie probably knows the solution for this

#5

I’ve looked at translation rules for SQL Server’s SELECT TOP n, but Postgres uses LIMIT n, whereas Oracle uses WHERE ROWNUM <= n. Especially the last one, where you may have to add an expression to an existing where clause, seems a bit tricky.

One workable solution for now is to wrap your query like this:
SELECT ROW_NUMBER() OVER (ORDER BY [some variable]) AS rn, * FROM ( [your query] ) tmp WHERE rn <= 100;

@Juan_Banda: Could you give a bit of context about why you need to select the top n?

#6

Indeed, the differences make it quite tricky. I have added to my code
something similar to what you recommend and wrapped the query with a
row_number filter. Thanks for the recommendation.

Use case: I define a list of observations to filter N patients and use them
as cases to train a model. Then I want to take N-random (hence our last
SQLrender conversation) patients as controls that are not in the set of all
potential cases.

#7

Hi all. You might be interested to know that SELECT TOP is now being supported by SqlRender (see the latest release).

Note: I currently only implemented translation of SELECT TOP in the outer query, so it won’t work inside a nested query. So this will not be translated:

SELECT ...
FROM (
SELECT TOP 10 ...
) subquery;
#8

@schuemie, Can you confirm that this also works in nested queries now? I thought i saw the rules applied for sub queries, but I wanted to confirm.

#9

Looking at the translation rules, I see implementations for nested (and unnested) SELECT TOP statements for all SQL dialects except Google BigQuery. According to the BigQuery SQL syntax the rules for BigQuery should be the same as for Postgres (ie. using LIMIT), but I don’t have a BigQuery instance to test this on.

t