CODEWITHSUNDEEP
sqlsql-servert-sql
response.write
response.write

Reputation: 175

Select all columns with distinct values from a table

I have a table that contains these columns ID, NAME, JOB what I want is to select one record of every distinct job in the table

from this table

ID NAME JOB
1  Juan  Janitor
2  Jun   Waiter
3  Jani  Janitor
4  Jeni  Bartender

to something like this

ID NAME JOB
1  Juan Janitor
2  Jun  Waiter
4  Jeni Bartender

Using distinct will allow me to select one distinct column but i want to select every column in the table, any one have an idea how?

Upvotes: 3

Views: 1421

Answers (4)

angfreak
angfreak

Reputation: 1003

SELECT ID, NAME,JOB FROM
(
    SELECT ID,Rank() Over(Partition By Job Order By ID) As jobRank, Name,JOB from #abhi
) AS TTable

WHERE jobRank = 1

Upvotes: 0

sameh.q
sameh.q

Reputation: 1709

SELECT MIN(ID), NAME, JOB FROM `table`
Group by NAME, JOB

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 1541

-- Fetch Only Rnk =1 Record to get desired output

SELECT ID,Rank() Over(Partition By Job Order By ID) As Rnk, Name,JOB
From EMP

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You may try this

SELECT ID, NAME,JOB FROM
(
SELECT ID, NAME,JOB,Row_Number() Over (Partition BY NAME Order By ID) AS RN FROM `table1`
) AS T

WHERE RN = 1

Upvotes: 3

Related Questions