Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
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
add comment

2 Answers 2

up vote 5 down vote accepted
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.

share|improve this answer
    
Thanks, the row_number() function was exactly what I was looking for –  david Nov 13 '12 at 10:26
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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