CODEWITHSUNDEEP
sqlpostgresql
LaprimoSiesta
LaprimoSiesta

Reputation: 205

SQL Argument of LIMIT must not contain variables

I have the tables A(ida PK,entry_year, . . .), B(idc PK,ida FK,value1, . . .) and C(year PK,value2, . . .) I'm trying to select the first n value1 from table B (associated with a specific ida of table A), where n is the value2 of table C with the same C.year as A.entry_year. The code is similar to this one:

select value1 from A as Alpha join B using(ida)
where A.ida=$1
limit (select value2 from C where year=(select entry_year from A where A.ida=Alpha.ida))

But i get the following error: Argument of LIMIT must not contain variables. What am I supposed to do?

thanks!

Upvotes: 4

Views: 3865

Answers (1)

Michel Milezzi
Michel Milezzi

Reputation: 11115

You can use row_number to simulate LIMIT.

SELECT * FROM (
    select 
        value1,
        entry_year,
        row_number() OVER() AS rownum --Probably you should use PARTITION BY here using student id if you want to retrieve more than one student
    from 
        A as Alpha 
        join B using(ida)
    where A.ida=$1) as tmp
JOIN C ON (C.year = tmp.entry_year)
WHERE rownum <= C.value2

Upvotes: 7

Related Questions