March 7, 2008 at 3:49 am
my data like this
id | username | event
1 a 1
2 b 1
3 a 2
4 c 1
5 a 3
6 c 2
7 b 2
8 c 3
9 b 3
i want group by username and order by max(id)
result like this
username id
---------------
b 9
b 7
b 2
c 8
c 6
c 4
a 5
a 3
a 1
---------------
if insert new data : 10 a 4
then result like this
username id
---------------
a 10
a 5
a 3
a 1
b 9
b 7
b 2
c 8
c 6
c 4
---------------
if insert new data : 11 b 4
then result like this
username id
---------------
b 11
b 9
b 7
b 2
a 10
a 5
a 3
a 1
c 8
c 6
c 4
---------------
sorry , my english is a little bit
and thank you very for answer me
March 7, 2008 at 7:41 am
here's one solution:
select A.username, A.id
from your_table as A
join (select username, max(id) as max_id
from your_table group by username) as B
on A.username = B.username
order by B.max_id desc, A.username, A.id desc
March 7, 2008 at 7:49 am
I have to know because it's a bit unusual... why do you need this type of sort?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2008 at 10:39 pm
antonio.collins (3/7/2008)
here's one solution:
select A.username, A.id
from your_table as A
join (select username, max(id) as max_id
from your_table group by username) as B
on A.username = B.username
order by B.max_id desc, A.username, A.id desc
oh!! thank you very much :w00t:
March 10, 2008 at 5:46 am
Yep... Antonio writes good stuff. Still, I'd like to know why you need this type of "sort", please.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 5:51 am
looks like he is trying to get the user with the most recent activity to the top of the resultset.
March 10, 2008 at 6:42 am
Heh... yeah... I can see that... my question would be why? And if what you say were true, why list all of the users instead of just the most recent?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 4:40 am
Very useful Query. Thanks a lot:)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply