2

I have an table with some records and a column LAST_EDIT_USER. If I want to select all users with the number of appearances for every user, I would do

SELECT   LAST_EDIT_USER,COUNT(LAST_EDIT_USER)
FROM     EDITED_ELEMENTS_TABLE
GROUP BY LAST_EDIT_USER
ORDER BY COUNT(LAST_EDIT_USER) ASC;

and this will return something like

NULL   0
user1  5
admin  7
USER2  11

How can I get from a SQL filter the user1 (with the minimum of occurrences) - excepting NULL? LATER EDIT: or a list of the users with minimum number of occurrences

4
  • 1
    Are you asking how to ONLY return 1 row which would be user1 because it has the minimum count where LAST_EDIT_USER is not null? Jul 3, 2018 at 11:55
  • 2
    Which DBMS are you using? And what happens if there are two users with the same lowest count (e.g.: user1 -> 5, user2 -> 5), return all of them or only one of them? If only one of them, what's the criteria which of them will be returned (e.g. lexicographical smallest user name)?
    – sticky bit
    Jul 3, 2018 at 11:56
  • Yap, I need a list of the users with the minimum number of uses, sorry
    – WDrgn
    Jul 3, 2018 at 11:57
  • - excepting NULL? - HAVING last_edit_user IS NOT NULL. I need a list of the users - it is necessary to specify DBMS.
    – Akina
    Jul 3, 2018 at 12:11

1 Answer 1

1

Add a WHERE clause to filter the rows where last_edit_user is null. Then put another query around it, to get the minimum count. Use this as a subquery in the HAVING clause to get only the ones with the count equal to the minimum.

SELECT last_edit_user,
       count(last_edit_user)
       FROM edited_elements_table
       WHERE last_edit_user IS NOT NULL
       GROUP BY last_edit_user
       HAVING count(last_edit_user) = (SELECT min(c)
                                              FROM (SELECT count(last_edit_user) c
                                                           FROM edited_elements_table
                                                           WHERE last_edit_user IS NOT NULL
                                                           GROUP BY last_edit_user) x);
3
  • do it needs an AS.... ? because it gives me an error.
    – WDrgn
    Jul 3, 2018 at 12:24
  • 1
    @WDrgn - try putting a table alias between the last 2 parenthesis - GROUP BY last_edit_user) a ); Jul 3, 2018 at 12:34
  • 1
    @WDrgn: Sorry, I missed to alias the subquery, you're right. I correct the answer, thanks for the pointer.
    – sticky bit
    Jul 3, 2018 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.