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

  • 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? – Scott Hodgin Jul 3 '18 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 '18 at 11:56
  • Yap, I need a list of the users with the minimum number of uses, sorry – WDrgn Jul 3 '18 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 '18 at 12:11
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);
  • do it needs an AS.... ? because it gives me an error. – WDrgn Jul 3 '18 at 12:24
  • 1
    @WDrgn - try putting a table alias between the last 2 parenthesis - GROUP BY last_edit_user) a ); – Scott Hodgin Jul 3 '18 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 '18 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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