CODEWITHSUNDEEP
sql-server
user2575443
user2575443

Reputation: 1

selecting distinct record using sql

I have a table named People Job (ID, People ID, Post).

With sample data.

ID   People ID      Post

1    P1         Office Assistant

2    P2         Assistant Manager

3    P3         General Manager

4    P1         Officer

5    P2         Manager

I want to get records as follows.

ID    People ID     Post

3    P3          General Manager

4    P1          Officer

5    P2          Manager

Upvotes: 0

Views: 49

Answers (3)

Linky
Linky

Reputation: 604

Not quite sure on your requirement, but in guess you want the data of the highest id for a specific people_id... this should fit:

select * 
from People_Job
where id in (
    select max(id)
    from People_Job
    group by people_id
);

I just saw GriGrim's post - if you're running on large data his version should perform better...

Upvotes: 1

Pierre
Pierre

Reputation: 430

SELECT * FROM `People Job` WHERE `ID People`>2 ORDER BY `ID People`

But your tables and column names should not contains spaces.

Upvotes: 0

GriGrim
GriGrim

Reputation: 2921

SELECT PJ.*
FROM People_Job PJ
INNER JOIN (
    SELECT People_ID, MAX(ID) MAX_ID
    FROM People_Job
    GROUP BY People_ID
) T ON PJ.People_ID = T.People_ID AND PJ.ID = T.MAX_ID

Upvotes: 3

Related Questions