CODEWITHSUNDEEP
sqldatabasepostgresql
Radek Simko
Radek Simko

Reputation: 16126

LIMIT/OFFSET by DISTINCT column

I need to select all posts with all their related tags:

SELECT p.*, pt.name AS tag_name, pt.id AS tag_id FROM posts p
LEFT JOIN posts_tags pt ON pt.post_id = p.id

So i get something like this:

 p.id | p.name | p.content | tag_name | tag_id
  1   | Yahoo  | ...       | first    | 1
  1   | Yahoo  | ...       | second   | 2
  2   | Google | ...       | second   | 2
  2   | Google | ...       | third    | 3

I know when selecting records this way it is possible to get number of records through COUNT(p.id) for example, but i didn't discover how to set OFFSET (how many records skip from the beginning) and LIMIT (how many records return at all) according to unique post ID.

Now it's obviously working in the way, that it's skipping/limiting the number of records, but not the number of real posts...

Upvotes: 7

Views: 6465

Answers (2)

pilcrow
pilcrow

Reputation: 58589

If I understand you correctly, you want all "tag" rows for N posts starting at offset O:

   SELECT p.*, pt.name AS tag_name, pt.id AS tag_id
     FROM (SELECT * FROM posts ORDER BY id ASC LIMIT {N} OFFSET {O}) p
LEFT JOIN posts_tags pt
          ON pt.post_id = p.id

Addendum

Here's one way to do it using DENSE_RANK to limit just by the posts themselves:

   SELECT p.id, p.name, p.content, pt.name as tag_name, pt.id AS tag_id
     FROM (SELECT DENSE_RANK() OVER (ORDER BY id) AS dr, posts.*
             FROM posts) p
LEFT JOIN posts_tags pt
          ON pt.post_id = p.id
    WHERE dr BETWEEN {N} AND {N + O}

Upvotes: 5

user565869
user565869

Reputation:

I'm not very familiar with PostgreSQL, but (if I understand your question right) I think ROW_NUMBER() is the right place to start. Something like this:

SELECT
  Tag_Name, Tag_ID
FROM
  (
  SELECT P.ID, P.Name AS Tag_Name, P.Content, ROW_NUMBER() OVER (ORDER BY P.ID, PT.Tag_ID) AS RowNum
  FROM Posts AS P LEFT JOIN Posts_Tags AS PT ON P.Post_ID = P.ID
  ) AS X
WHERE
  RowNum BETWEEN 101 AND 200

This should do the trick in MS SQL, you may need to adjust the syntax.

Upvotes: 2

Related Questions