CODEWITHSUNDEEP
sql
Taras Kudrych
Taras Kudrych

Reputation: 31

SQL query with ORDER before GROUP?

Table "comments":

id    post_id  data
1     1        1
2     1        2
3     2        3
4     2        4
5     3        5
6     3        6
7     4        7
8     4        8

Need result table:

id    post_id  data
2     1        2
4     2        4
6     3        6
8     4        8

So as you see, I need to group by post.id, match last comment to each post and add comment data.

Upvotes: 0

Views: 72

Answers (4)

Taras Kudrych
Taras Kudrych

Reputation: 31

Here I found really working query:

SELECT * FROM posts 
LEFT JOIN comments ON comments.post_id = posts.id  
WHERE comments.id = (
    SELECT MAX(c.id) 
    FROM comments as c 
    WHERE c.post_id = posts.id
) 
OR comments.id IS NULL

Upvotes: 0

TYY
TYY

Reputation: 2716

CREATE TABLE Comments
(
  id int,
  post_id int,
  data int
)

INSERT INTO Comments
VALUES
  (1,     1 ,       1),
  (2,     1   ,     2),
  (3,     2,        3),
  (4,     2,        4),
  (5,     3,        5),
  (6,     3,        6),
  (7,     4,        7),
  (8,     4,        8)


SELECT * INTO #Comments
FROM
(
  SELECT post_id, id, data, RANK() OVER 
      (PARTITION BY c.post_id ORDER BY c.id ASC) AS Rank
  FROM Comments c
 )a

-- SELECT * FROM #Comments

SELECT  ac.post_id, ac.id, ac.data
FROM #Comments ac
INNER JOIN
(
  SELECT post_id,  Max(Rank)Rank
  FROM #Comments c
  GROUP BY post_id
)c ON c.post_id = ac.post_id AND c.Rank = ac.Rank
ORDER BY ac.post_id

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425341

SELECT  MAX(id), post_id
FROM    mytable
GROUP BY
        post_id

If you need columns other than id (say, the whole record with the greatest id group-wise), use this:

SELECT  m.*
FROM    (
        SELECT  DISTINCT post_id
        FROM    mytable
        ) md
JOIN    mytable m
ON      id =
        (
        SELECT  id
        FROM    mytable mi
        WHERE   mi.post_id = md.post_id
        ORDER BY
                mi.post_id DESC, mi.DESC
        LIMIT 1
        )

Create a composite index on mytable (post_id, id) for this to work fast.

Upvotes: 1

Azar
Azar

Reputation: 1867

Select max(id) id,post_id
from table
group by post_id

Upvotes: 2

Related Questions