CODEWITHSUNDEEP
sql
Reza Kahfi
Reza Kahfi

Reputation: 107

select top 1 record for each record on 1 table

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 ?

Upvotes: 4

Views: 3813

Answers (8)

Jade
Jade

Reputation: 2992

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.

Upvotes: 4

Edper
Edper

Reputation: 9322

Updated Answer:

SELECT p.id, p.name, j.job, j.dateStart
FROM tbl_people p
INNER JOIN
(
 SELECT a.id, b.job, a.dateStart
 FROM (
 SELECT id, MAX(date_start) as dateStart
 FROM tbl_job
 GROUP BY id
 ORDER BY MAX(date_start) DESC
) A
INNER JOIN tbl_job B
ON A.id = B.id and A.dateStart = B.date_start
) j
ON p.id = j.id

Sql Fiddle Demo

Upvotes: 1

jnsr_acumen
jnsr_acumen

Reputation: 61

try this

select id_s,job,date_s,name 
from (select id as id_s, max(date_start) as date_s from job group by id),job j,people p
where id_s=j.id and id_s=p.id and date_s=j.date_start;

Hope this will solve the problem.

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT S.id,s.name,S.job,s.date_start
FROM
(
  SELECT T2.id,T1.Name,T2.job,T2.date_start
  from people T1 Left Join job T2 ON T1.id =T2.id
  group by T2.date_start,T2.id,T1.name,T2.job
) AS S Inner JOIN 
( 
  SELECT Max(date_start) AS date_start,id From job 
  Group by id
) AS T ON S.date_start = T.date_start
Order By S.ID

Fiddle Demo

Output : enter image description here

Upvotes: 2

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

select j.id,name,(Select k.job from job k where k.date_start=
max(j.date_start) and k.id=j.id ) as 'job', max(j.date_start) as 'date_start'
from people p inner join job j on j.id=p.id group by p.name,p.id
having max(j.date_start) order by j.id

Upvotes: 1

Sathish
Sathish

Reputation: 4487

Try Like This 

 Select id,name,job,date_start from pepople p,job j,(select id,max(date_start) 
 as maxdate from job) as tbl where p.id=j.id and j.id=tbl.id and 
 j.date_start=tbl.maxdate

Upvotes: 1

JDGuide
JDGuide

Reputation: 6525

Try this :-

select p.id,p.name,j.job,j.date_start
from people p left join job j on p.id=j.id
group by p.id order by max(j.date_start) desc;

Hope it will help you.

Upvotes: 1

domdomcodecode
domdomcodecode

Reputation: 2443

You could try a GROUP BY for this.

SELECT p.id, p.name, j.job, MAX(j.date_start)
FROM people p
    INNER JOIN job j
        ON p.id= j.id
GROUP BY p.id, p.name, j.job

Upvotes: 1

Related Questions