CODEWITHSUNDEEP
sqldistinct
Rami Alshareef
Rami Alshareef

Reputation: 7140

SQL Query: Get rows (all columns) from table basied on one distinct column

this is a sample data to explain my case:

alt text

I want to get data distinct by EMPLOYID, so only 5199, 6000, 6100 employees returned, and for the rest of columns what ever data returned it would be OK

My try: I planned to get distinct EMPLOYID first then get top 1 for each Employee but it seems need a loop, and in my case i restricted to only select query.

Upvotes: 0

Views: 1930

Answers (3)

m.edmondson
m.edmondson

Reputation: 30872

I haven't tested it but I think this should do the trick:

declare @DISTINCTEMPLOYIDS table
    (IDS  char(8)) --whatever datatype employid is

insert into @DISTINCTEMPLOYIDS (IDS)
select distinct EMPLOYID
from TABLENAME

select *
from TABLENAME as t
join @@DISTINCTEMPLOYIDS as d
on t.EMPLOYID = d.EMPLOYID

Remember to change TABLENAME to whatever the name of your table is

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

Assuming SQL Server 2005 or later, here's a basic example to demonstrate one approach:

DECLARE @Data TABLE (ID INTEGER, NameField VARCHAR(10))
INSERT @Data VALUES (1, 'A')
INSERT @Data VALUES (1, 'B')
INSERT @Data VALUES (1, 'C')
INSERT @Data VALUES (2, 'A')
INSERT @Data VALUES (3, 'C')
INSERT @Data VALUES (3, 'B')

SELECT ID, NameField FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RowNo, *
FROM @Data
) x
WHERE RowNo = 1

Here's the reference on ROW_NUMBER

Upvotes: 3

sasfrog
sasfrog

Reputation: 2460

try SELECT * FROM myTable GROUP BY EMPLOYID

Upvotes: 0

Related Questions