CODEWITHSUNDEEP
postgresqlinner-joinlimit
User5644
User5644

Reputation: 5

Using LIMIT Statement in INNER JOIN (postgreSQL)

I am having trouble using the LIMIT Statement. I would really appreciate your help. I am trying to INNER JOIN three tables and use the LIMIT statement to only query a few lines because the tables are so huge.

So, basically, this is what I am trying to accomplish:

SELECT *
FROM ((scheme1.table1
INNER JOIN scheme1.table2
ON scheme1.table1.column1 = scheme1.table2.column1 LIMIT 1)
INNER JOIN scheme1.table3
ON scheme1.table1.column1 = scheme1.table3.column1)
LIMIT 1;

I get an syntax error on the LIMIT from the first INNER JOIN. Why? How can I limit the results I get from each of the INNER JOINS. If I only use the second "LIMIT 1" at the bottom, I will query the entire table.

Thanks a lot!

Upvotes: 0

Views: 3291

Answers (1)

user330315
user330315

Reputation:

LIMIT can only be applied to queries, not to a table reference. So you need to use a complete SELECT query for table2 in order to be able to use the LIMIT clause:

SELECT *
FROM schema1.table1 as t1
  INNER JOIN (
     select *
     from schema1.table2
     order by ???
     limit 1
  ) as t2 ON t1.column1 = t2.column1 
  INNER JOIN schema1.table3 as t3 on ON t1.column1 = t3.column1
order by ???
limit 1;

Note that LIMIT without an ORDER BY typically makes no sense as results of a query have no inherent sort order. You should think about applying the necessary ORDER BY in the derived table (aka sub-query) and the outer query to get consistent and deterministic results.

Upvotes: 1

Related Questions