Count unique ocurrence sorted by date in myqsl

Question

0.00/5 (No votes)

See more:

Howdy,

I'm new here and hope that someone can help me with my issue.
I have a toplist which shows unique lakes (mm) for each user (mycall) in a table that also tracks confirmed lakes (conf = 1). Grouping by mycall and ordering by total gives me the toplist.
Now for my problem. I need the list to also be sorted by the time. The table has a datetime column (tid). I need the date for the latest unique count.

Example table

mycall ,tid, mm, conf
SM6ABC, 2022-04-01 09:00:00, AAAA, 1
SM6ABC, 2022-04-01 09:01:00, AAAB, 1
SM6ABC, 2022-04-01 09:12:00, AAAA, 1
SM6CBA, 2022-04-01 09:10:00, BBBB, 1
SM6CBA, 2022-04-01 09:11:00, AAAA, 1
SM6AAA, 2022-04-01 09:12:00, AAAA, 1

Output should be

SM6ABC, 2 (Last unique data is SM6ABC, 20220401 09:01:00, AAAB, 1)
SM6CBA, 2 (Last unique data is SM6CBA, 2022-04-01 09:11:00, AAAA, 1)
SM6AAA, 1

What I have tried:

MySQL

SQL

SELECT mycall, COUNT(DISTINCT mm) AS 'total' FROM KJ_log WHERE mm !='' AND conf = '1' GROUP BY mycall ORDER BY total DESC

This works fine but how do I include the datetime sorting? I tried to add select MAX(tid) but that returns last date for the user.

Posted 20-Apr-22 22:23pm

SM6TOB

Updated 21-Apr-22 9:24am

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2022-04-21T05:35:00

Solution 1

You can add several fields/columns into ORDER BY clause:

SQL

ORDER BY <Field1> [SortOrder1], <Field2> [SortOrder2]

See: MySQL ORDER BY[^]

If you wan to include last date, try this:

SQL

SELECT mycall, MAX(tid) AS 'tid', COUNT(DISTINCT mm) AS 'total'
FROM KJ_log
WHERE mm !='' AND conf = '1'
GROUP BY mycall
ORDER BY total DESC

An alternative way is to use window functions: MySQL :: MySQL 8.0 Reference Manual :: 12.21 Window Functions[^]
MySQL Window Functions: An Essential Guide to Window Functions in MySQL[^]

[EDIT]
Your tid column contains incorrect data type / format!
See this demo: MySQL 8.0 | db<>fiddle[^]

Posted 21-Apr-22 5:35am

Maciej Los

Updated 21-Apr-22 9:07am

v2

Comments

SM6TOB 21-Apr-22 14:29pm

Unfortunately your solution returns thw wrong answer :( I have also tried the MAX(tid) but then the date of (SM6ABC, 20220401 09:12:00, AAAA, 1) will be used. Look at output above.

Abt Window functions - never heared of but will hav a look at that too!

Maciej Los 21-Apr-22 15:07pm

See updated answer.

SM6TOB 21-Apr-22 15:25pm

tid corrected

Maciej Los 21-Apr-22 15:40pm

And...

SM6TOB 22-Apr-22 1:39am

You reply "And...", I say and what???
Problem still remains!
Have a look at my output above. You will not get that with your MAX(tid) solution.

Maciej Los 22-Apr-22 15:45pm

You're joking, right?
Take a deep look at your input data and expected output. The last record for SM6ABC is with this time: 2022-04-01 09:12:00. If you STILL say i'm wrong, then i don't know what you mean by "last date/time".

SM6TOB 22-Apr-22 16:04pm

Never joke about SQL query ;-)
I try to explain a bit furter.

SM6ABC, 2022-04-01 09:00:00, AAAA, 1
SM6ABC, 2022-04-01 09:01:00, AAAB, 1
SM6ABC, 2022-04-01 09:12:00, AAAA, 1

SM6ABC occurs 3 times. I need to count distinct rows of "mm"-column (3:rd column in table). AAAA is the 1:st, AAAB is the 2:nd and for this row i need the date "2022-04-01 09:01:00" to be returned by the query. The 3:d row has AAAA again which is not unique. Hope you understand :-)

SM6TOB 23-Apr-22 13:20pm

I dont know the practise here, but i have a way for a solution. This could be a new question but i'll add it here.

I have a beast of a query which gives me a total unique areas and the date for te last unique one.
How do i alter/rewrite this query to list all users(mycall) in the table? Current query lists only selected user.

Important in table:
Datetime, sm, mm, conf
Areacode can be in both sm and mm but is counted uniqely
Fooled about with some subquerys / left join but how can i pass values to "mycall"?

SELECT COUNT(mycall), MAX(KJ_tid) FROM (SELECT t1.*, KJ_log.tid AS KJ_tid, KJ_log.sm AS KJ_sm, KJ_log.mm AS KJ_mm FROM (SELECT DISTINCT mycall, sm FROM KJ_log WHERE mycall = 'SM6TPJ' AND sm !='' AND conf = '1' UNION DISTINCT SELECT mycall, mm FROM KJ_log WHERE mycall = 'SM6TPJ' AND mm !='' AND conf = '1') AS t1 LEFT JOIN KJ_log ON (t1.sm = KJ_log.sm) OR (t1.sm = KJ_log.mm) where KJ_log.mycall = 'SM6TPJ' AND conf = '1' GROUP BY t1.sm) AS latestdate

0x01AA 21-Apr-22 16:00pm

My small 5.

Maciej Los 21-Apr-22 16:01pm

Thank you, Bruno.