Order Sort by joined tbl data

Skatan

I got a table of users, and a second table with the log history of the users. I would like to fetch a list of all users with sorted by the user that was logged in last.
But I dont a proper SQL-select for this.

Tables:
tbl_users
id | firstname

tbl_logins
id | user_id | date

My idea of the query:

SELECT *
FROM users AS u
LEFT JOIN logins AS l
ON l.user_id=u.id
GROUP BY l.user_id
ORDER BY l.time DESC

It's not working, I dont get the correct sorting

Derokorian

according to your table definition your log column name is date however you are trying to order by a column called time.

bradgrafelman

Welcome to PHPBuilder!

Note that if Derokorian's observation does not resolve your issue, you might want to supply us with the exact SQL and/or PHP error message(s) you're getting; diagnostically speaking, "it's not working" tells us absolutely nothing. (We already know it's not working - that's why you're here, after all. 😉)

Skatan

Thx for you replies.

I don' get any error, it just not order it in the right way.
I want the user with most recent logg entry to be on first.

I think the problem is that SQL is not selecting the LIMIT 1 of the users logg entiries.
If I was possible to set LIMIT() on the logg table I that would been the solution but its not working.

johanafm

LEFT JOIN (
	SELECT user_id, MAX(logins.logindate) as logdate
	FROM logins
	GROUP by user_id
) AS l ON l.user_id = user.id

Skatan

johanafm;10997733 wrote:

LEFT JOIN (
	SELECT user_id, MAX(logins.logindate) as logdate
	FROM logins
	GROUP by user_id
) AS l ON l.user_id = user.id

Johanafm, do you mean to use with togheter with the other leftjoin?
Im working with Zend Framework and got this, but still the result not order correctly:

SELECT rma., logg., date.rma_entity_id, MAX(date.created_at)
FROM rma_entity AS rma
LEFT JOIN rma_logg AS logg ON rma.entity_id=logg.rma_entity_id
LEFT JOIN rma_logg AS date ON date.logg_id=logg.logg_id
GROUP BY rma.entity_id
ORDER BY logg.created_at DESC

The problems seemse to be when its more then one logged entry for any of the posts.

Do you see where I doing wrong?

johanafm

Skatan;10997789 wrote:
Johanafm, do you mean to use with togheter with the other leftjoin?

No, instead of. As you can see, this will LEFT JOIN on a query which only selects data from logins. You may run the nested query separately to see the data retrieved by it, which is one entry per user_id, and that entry will contain MAX(logindate). Thus, you have selected one login per user, and the selected login is the latest login.

So you replace your previous join on the logins table with this one.

Skatan

Great!
And import was to set MAX(date.created_at) into the ORDER BY and not only date.created_at

Final SQL that works for me, thanks to you guys!

SELECT `rma`.*, `date`.`rma_entity_id`, MAX(date.created_at) 
FROM `rma_entity` AS `rma` 
LEFT JOIN `rma_logg` AS `date` 
ON rma.entity_id=date.rma_entity_id 
GROUP BY `date`.`rma_entity_id` 
ORDER BY [B]MAX(date.created_at)[/B] DESC