PostgreSQL - INNER JOIN two tables with a LIMIT

Question

I've seen this post which almost coincides with my question but my specific problem is that I need to put a limit to the third table/query, as in LIMIT 15, for example. Is there an easy way to achieve this? Thanks!

EDIT

My SQL SELECT statement would look something like this:

SELECT t2.name AS user_name, t3.name AS artist_name
FROM tbl1 t1
    INNER JOIN tbl2 t2 ON t1.t1able_id = t2.id
    INNER JOIN (SELECT * FROM tbl3 WHERE artist_id = 100 limit 15) t3
        ON t2.id = t3.artist_id
WHERE t1.kind = 'kind'

To clarify: It's just a matter of joining two tables but the second table has two states. First state as a "common user" and the next state as an "artist" (both using the same table, e.g. users).

t2.id represents a common user and t2.artist_id represents the artist of a common user? there exist more than one artist for one common user? — felipe.zkn, Jun 03 '13 at 11:14
If you want, we can use this [fiddle](http://sqlfiddle.com/#!2/517df/3) to play and you could correct me — felipe.zkn, Jun 03 '13 at 11:19
Here's the edited [fiddle](http://sqlfiddle.com/#!2/2e47a/1) — MoMo, Jun 03 '13 at 11:52

Pritesh Tayade · Answer 1 · 2013-06-03T08:52:33.430

5

Try this query:

select *
from
    tableA a
        inner join
    tableB b
        on a.common = b.common
        inner join 
    (select * from tableC order by some_column limit 15) c
        on b.common = c.common

edited Jun 03 '13 at 08:52

answered Jun 03 '13 at 08:46

Pritesh Tayade

630
4
11

2

When using `limit` you should also apply an `order by` otherwise the result is "random" – Jun 03 '13 at 08:47
You can use MAX or MIN too to limit for a specific table. – Kees Sonnema Jun 03 '13 at 08:54
I'm sorry, I just realized it's not using three tables but two. I have edited my question to be more specific. – MoMo Jun 03 '13 at 09:05
1

@KeesSonnema As per my understanding aggregate functions like `MAX() MIN()` will return only 1 row , it is not a alternate for `limit`.. as `limit` restricts the `resultset` to specified number. – Pritesh Tayade Jun 03 '13 at 12:23

score 0 · Answer 2 · answered Mar 17 '20 at 10:15

Few days ago I searched for such answer and Postgresql provides to do it smoothly with rank(). My goal was to get last 3 submitted annual report files before commercial firm got started its first insolvency process.

SELECT * FROM
(
    SELECT r.id, f.id AS id_file, f.file_date, min(p.process_started) AS "first_process_started",
    rank() OVER (PARTITION BY r.id ORDER BY f.file_date DESC) AS "rank"
    FROM registry r
      INNER JOIN files f ON (r.id = f.id_registry)
      INNER JOIN processes p ON (r.id = p.id_registry)
    WHERE 
      r.type = 'LIMITED_LIABILITY_COMPANY'
      AND f.file_type = 'ANNUAL_REPORT')
      AND p.process_type = ('INSOLVENCY')
    GROUP BY r.id, f.id, f.file_date
    HAVING f.file_date <= min(p.process_started)
) AS ranked_files
WHERE rank <= 3

PostgreSQL - INNER JOIN two tables with a LIMIT

2 Answers2

Linked