SQL - select individual records in one field with the highest records from another field

Question

In a scenario where I have a table, for example:

int id (PK) int staff_id int skill_id bit mainskill 

I want to select only one entry for each employee (represented by staff_id), which lists their basic skills presented in (1) in mainskill. If the core skill is missing, I want to return any of the skill entries for this employee. For instance:

 id staff_id skill_id mainskill 1 1 24 1 2 1 55 0 3 1 7 0 4 4 24 0 5 4 18 0 6 6 3 0 7 6 18 1 

The request should be returned:

 id staff_id skill_id mainskill 1 1 24 1 4 4 24 0 7 6 18 1 

I tried various combinations of grouping, DISTINCT, etc., but cannot get the result that I get. Any help was appreciated.

Answer 1

SQL Server 2005+ using CTE:

---

 WITH rows AS ( SELECT t.id, t.staff_id, t.skill_id, t.mainskill, ROW_NUMBER() OVER (PARTITION BY t.staff_id ORDER BY t.mainskill DESC) AS rank FROM TABLE t) SELECT r.id, r.staff_id, r.skill_id, r.mainskill FROM rows r WHERE r.rank = 1 ORDER BY r.staff_id 

SQL Server 2005+, non-CTE equivalent:

---

  SELECT r.id, r.staff_id, r.skill_id, r.mainskill FROM (SELECT t.id, t.staff_id, t.skill_id, t.mainskill, ROW_NUMBER() OVER (PARTITION BY t.staff_id ORDER BY t.mainskill DESC) AS rank FROM TABLE t) r WHERE r.rank = 1 ORDER BY r.staff_id 

Both use ROW_NUMBER , which is only available with SQL Server 2005.

Answer 2

If you combine mainskill on the front of the skill, max will give you either mainskill or another where mainskill does not exist.

SELECT t.id, t.staff_id, t.skill_id, t.mainskill, FROM TABLE t WHERE CAST (t.mainskill As Varchar (5)) + '-' + Cast (t.skill_id as varchar (5)) ( SELECT MAX (CAST (t.mainskill As Varchar (5)) + '-' + Cast (t.skill_id as varchar (5))) FROM TABLE t GROUP BY t.staff_id)

Answer 3

MySQL

 select * from staff_skill; id staff_id skill_id mainskill ---------- ---------- ---------- ---------- 1 1 24 1 2 1 55 0 3 1 7 0 4 4 24 0 5 4 18 0 6 6 3 0 7 6 18 1 7 rows selected select * from staff_skill x where skill_id = (select y.skill_id from staff_skill y where y.staff_id = x.staff_id order by y.mainskill desc, y.skill_id desc limit 1); id staff_id skill_id mainskill ---------- ---------- ---------- ---------- 1 1 24 1 4 4 24 0 7 6 18 1 3 rows selected 

- Ian

Answer 4

Oracle

What about:

(staff_skill is your table)

 select * from staff_skill x where skill_id = (select skill_id from (select * from staff_skill order by mainskill desc, skill_id desc) where staff_id = x.staff_id and rownum = 1);