Select the newest data (inner join + group by maybe)

Question

I think I would need some help. To simplify my problem, lets assume that I have two tables. First called "topics" with this structure:

idTopic  author  deleted
1        123     false
2        132     false

and second callledd "texts" which looks like this:

idTopic idText datePosted
1       11     1
1       12     2
2       15     1
2       18     3

and what I need is to select the data for every topic with the data of newest text of this topic. So my output should be like this:

idTopic author deleted idText datePosted
1       123    false   12     2
2       132    false   18     3

so how should my sql query looks? I was trying to do some inner join with second table grouped by idTopic, but it didn't work.

Thanks

Answer 1

select idTopic, author, deleted, idText, datePosted
from
(
    select t.idTopic, t.author, t.deleted, x.idText, x.datePosted,
           row_number() over (partition by t.idTopic order by x.datePosted desc) rn
    from topics t
    left join texts x on t.idTopic = x.idTopic -- or INNER JOIN
) z
where rn=1

1. Check out the ROW_NUMBER() function which is used to give a unique sequential number to each row in each "partition". In this case partition by t.idTopic tells the function to independently number each set of records of the same t.idTopic.

2. Within each partition, the records are numbered 1,2,3... based on a specific order, which in this case is order by x.datePosted desc - this puts the newest posts first.

3. LEFT JOIN also known in full as "LEFT OUTER JOIN" is used to keep all records of the table on the left side even if there are no records from the right. If you don't need to see a topic if there are no texts for it, simply drop the "LEFT" keyword.

4. The query is moved into a derived table and aliased (unimportantly as z) so that we can filter on it for where rn = 1. Recall that each partition can only have one row of value rn=1, which is by definition of our ORDER BY clause in ROW_NUMBER(), the row with the latest text.

Answer 2

The easiest way to do this is with a CTE (Common Table Expression) that employs the ROW_NUMBER function to rank the idtopics by the posted date. Something like:

;WITH ranked AS
(
SELECT
    idTopic,
    idtext,
    datePosted,
    ROW_NUMBER() OVER (PARTITION BY idtopic ORDER BY datePosted DESC) AS rowNum
FROM
    dbo.texts
)
SELECT
    T.idTopic,
    T.author,
    T.deleted,
    R.idtext,
    R.datePosted
FROM
    ranked R
INNER JOIN dbo.topics T
    on R.idTopic = T.idTopic
WHERE
    rownum = 1