0

Let's say I've just carried out this query upon a table to achieve this output:

QUERY:

SELECT staff_id, tablea_A.item_id, item_type, status, date_installed 
FROM table_A 
INNER JOIN table_B ON table_A.item_id = table_B.item_id
Group By staff_id, tablea_A.item_id, item_type, status, date_installed

OUTPUT:

staff_id item_id item_type status date_installed
1 S4193 Ball Active 17/09/2021
1 S5567 Ball Active 22/07/2021
4 S9088 Ball Reserved 21/12/2021
7 J8877 Club Active 03/03/2016

..etc

Now that I've created a table which is grouped primarily by staff_id, I would like to display a single item per staff member, by prioritising this criteria in this order:

a. balls over any other item

b. active status over any other status

c. most recently installed items if date is provided

I've read about using the HAVING clause, but I wouldn't know how to implement it in this sense with a particular criteria. Any help would be hugely appreciated.

Stu
  • 30,392
  • 6
  • 14
  • 33
Velvetaura
  • 47
  • 2
  • You don't do any aggregations so it's not clear why you are grouping at all? Maybe look at [MySql top 1 row in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – Stu Aug 10 '22 at 15:30
  • I want there to be a single item of staff per item primarily but that single person needs to be selected based upon the criteria I identified – Velvetaura Aug 10 '22 at 15:42

0 Answers0