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

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.

Count unique ocurrence sorted by date in myqsl

1 solution

Solution 1

Add your solution here

Preview 0

Count unique ocurrence sorted by date in myqsl

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us