select top 1 record for each record on 1 table

Question

I have problems on query. I need the result that shows top 1 for each records from the table that has same id. Here is the Table Description.

Table people:

id   | name  |
--------------
01   | john  |
02   | alex  |

Table job:

id   | job       | date start  |
---------------------------------
01   | student   | 1/1/2013    |
01   | employee  | 1/1/2014    |
01   | manager   | 3/18/2014   |
02   | student   | 3/1/2013    |
02   | engineer  | 3/3/2014    |

I need the result showed 1 row for each id.

Here is my SQL query :

select id,name,job,date_start
from people,job
group by date_start,id,name,job

I got wrong result from that query. I don't know how to show just only 1 record for each id.

Here is the result I want :

Query result

id | name | job     | date_start |
----------------------------------
01 | john | manager | 3/18/2014  |    -> max date from id='01'
02 | alex | engineer| 3/3/2014   |    -> max date from id='02'

It's ordered by date and selected only 1 result from each id with the max date.

How can I do this ?

Answer 1

Try this

WITH j AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY id  ORDER BY date_Start DESC) AS RowNumber,
        id, job, date_start
    FROM job
)
select  p.id, p.name, j.job, j.date_start
from    people p
        inner join j
            on p.id = j.id
            and j.RowNumber = 1

As you requested..

• OVER is just like a grouping and sorting area in your select query but it only uses by a functions like ROW_Number()/RANK()/DENSE_RANK()/NTILE(n) and the like. see here for more info
• PARTITION BY is like having a group, in my example i partitioned or grouped the rows by id
• ORDER By is like having order by clause in the select query

ROW_Number() is a function in SQL Server that produces a sequence of integer value from 1 to N (up to the last record) and we reset the number generated by ROW_Number() function each time the PARTITION BY area changes its values.