Show unique ID's in a table with all extra info

Question

SELECT Personeelsnummer, Achternaam, Voornaam, Departement, SubDep, SubSubDep, FTE, RedenUitDienst, Anciennitëitsdatum, GeldigOp, Schrapping, Ancienniteit, Positie, Nieveau, OmschrijfingStatuut
FROM tbl_Worker
GROUP BY Personeelsnummer

OR

SELECT (DISTINCT Personeelsnummer), Achternaam, Voornaam, Departement, SubDep, SubSubDep, FTE, RedenUitDienst, Anciennitëitsdatum, GeldigOp, Schrapping, Ancienniteit, Positie, Nieveau, OmschrijfingStatuut
FROM tbl_Worker
GROUP BY Personeelsnummer

I have a worker table with 49000 records, this includes a 'snapshot' from all workers EVERY month. But what I need is a table with all employees the company 'ever' had but only once. so I tried to wright the query's show above but they are not working. So what I need is a query that shows all unique 'Personeelsnummers' with all the extra information about these persons.

what does work is this: SELECT DISTINCT Personeelsnummer FROM tbl_Worker ==> this gives me a table with 1200 records but only the numbers but I need all the extra information.

So if an employee has 20 rows since they have been there 20 months, which row do you want returned? — dfundako, Mar 16 '16 at 15:54
Possible duplicate of [SQL/mysql - Select distinct/UNIQUE but return all columns?](http://stackoverflow.com/questions/6127338/sql-mysql-select-distinct-unique-but-return-all-columns) — Matheno, Mar 16 '16 at 15:56
preferably the last one but that's not so important they should be more or less the same — Jente, Mar 16 '16 at 15:57

Gordon Linoff · Accepted Answer · 2016-03-16T16:21:14.113

0

Instead of GROUP BY, use WHERE to get the first or last record:

SELECT w.*
FROM tbl_Worker as w
WHERE monthcol = (SELECT MAX(w2.monthcol)
                  FROM tbl_Worker as w2
                  WHERE w2.Personeelsnummer = w.Personeelsnummer
                 );

You would use MIN() to get the first month's record. My Dutch is a bit weak, so I don't know which column refers to the date for the record.

For performance, you want an index on tbl_Worker(Personeelsnummer, GeldigOp):

create index idx_tbl_worker_Personeelsnummer_GeldigOp on tbl_Worker(Personeelsnummer, GeldigOp);

EDIT:

Or you can do it with a JOIN:

SELECT w.*
FROM tbl_Worker as w INNER JOIN
     (SELECT Personeelsnummer, MAX(GeldigOp) as max_GeldigOp
      FROM tbl_Worker
      GROUP BY Personeelsnummer
     ) as ww
     ON ww.Personeelsnummer = w.Personeelsnummer and ww.max_GeldigOp = w.GeldigOp;

edited Mar 16 '16 at 16:21

answered Mar 16 '16 at 16:00

Gordon Linoff

1,242,037
58
646
786

I am dutch, but I do not see any date record either ;-) – Matheno Mar 16 '16 at 16:02
Thank you, There is a "GeldigOp" column, and the query should give the right output but the problem with the nested query is the size of the table ==> 49000 * 49000 my pc can't handle it :p – Jente Mar 16 '16 at 16:11
That one worked! I also did it in the meanwhile but I just did it in two steps, I used a table as extra step, but the INNER JOIN is much prettyer, Thanks! – Jente Mar 16 '16 at 16:27

score -1 · Answer 2 · edited May 23 '17 at 11:50

-1

You're looking for a group by:

select *
from table
group by field1

Which can occasionally be written with a distinct on statement:

select distinct on field1 *
from table

As seen in this topic.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 16 '16 at 15:55

Matheno

4,112
6
36
53

I saw the topic, they don't work. I don't know if it's do to the version of MS Access or problems with the table but both query's don't work: – Jente Mar 16 '16 at 16:00

Show unique ID's in a table with all extra info

2 Answers2