Is this a Custom Query? If so, how?

Wappler General Need Help
1

Here is an example of what I need to do. This is an example of my table data:

ID. Date
1 2020-04-14
3 2020-04-13
1 2020-04-12
2 2020-04-11

Would like the following results:

ID. Most Recent Date Count
1 2020-04-14 2
3 2020-04-13 1
2 2020-04-11 1

I just can’t get my head around how to write this query?

2

try please
select id as 'ID' , date as 'Most Recent Date', count(id) as count from xyz group by date

3

But I need to get a distinct in there somewhere don’t I? For the ID field?

4

no just use “group by”

5

Hmmm, never thought of Group By … been trying with Distinct. I’ll give that a go, thank you!

6

Thanks Serhat, you got me very close. I can now display everything I need to display except it is displaying the first time a client logs in instead of the last time. Here is my SQL statement:

SELECT
	t_trackervisits.track_time, 
	t_trackervisits.track_clientid, 
	c_clients.client_first, 
	c_clients.client_last,
	COUNT(t_trackervisits.track_clientid) AS Visits
FROM
	t_trackervisits
	INNER JOIN
	c_clients
	ON 
	t_trackervisits.track_clientid = c_clients.client_random
GROUP BY t_trackervisits.track_clientid
ORDER BY Visits DESC, t_trackervisits.track_time DESC

Here is my result. It’s all good except the date should be the last time the user logged in not the first time.

Screen Shot 2020-04-14 at 1.24.51 PM
Screen Shot 2020-04-14 at 1.24.51 PM1066×548 118 KB

7

You are sorting by visits Brad:
ORDER BY Visits DESC

8

Sorting by visits , but needed to show the most recent date client logged in. I have it now. I was thinking too hard. I just needed to use a MAX() statement and it all works awesome now!

SELECT
	MAX(t_trackervisits.track_time) AS Last, 
	t_trackervisits.track_clientid, 
	c_clients.client_first, 
	c_clients.client_last,
	COUNT(t_trackervisits.track_clientid) AS Visits
FROM
	t_trackervisits
	INNER JOIN
	c_clients
	ON 
	t_trackervisits.track_clientid = c_clients.client_random
GROUP BY t_trackervisits.track_clientid
ORDER BY Visits DESC, t_trackervisits.track_time DESC
2 Likes