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.


  • 3
    What have you tried so far? I can write the query for you in less time than it takes to write this comment, but I'd rather help you learn – Philᵀᴹ Nov 13 '12 at 2:00
select idTopic, author, deleted, idText, datePosted
    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.

  • Thanks, the row_number() function was exactly what I was looking for – david Nov 13 '12 at 10:26

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
    ROW_NUMBER() OVER (PARTITION BY idtopic ORDER BY datePosted DESC) AS rowNum
    ranked R
INNER JOIN dbo.topics T
    on R.idTopic = T.idTopic
    rownum = 1

You can do it as below as well:

select IdTopic,Author,deleted,IdText,DatePosted from 
(select dense_rank() over (partition by c.IdTopic order by t.DatePosted desc) as DenseRank,
from Texts t inner join Topics c 
on t.IdTopic=c.IdTopic) CustOrders
where CustOrders.DenseRank= 1
  • 1
    Can you explain how this is different (or better?) than the other 2 answers? Is it only changing the window function from ROW_NUMBER() to DENSE_RANK()? – ypercubeᵀᴹ Jun 6 '18 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.