How to order by in this case?

Question

Post reply

How to order by in this case?

komnet

Grasshopper

Points: 16
More actions
March 7, 2008 at 3:49 am

#183473

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

I have to know because it's a bit unusual... why do you need this type of sort?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

komnet Grasshopper Points: 16 More actions · Answer 3

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:

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Yep... Antonio writes good stuff. Still, I'd like to know why you need this type of "sort", please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

twalston Mr or Mrs. 500 Points: 505 More actions · Answer 5

looks like he is trying to get the user with the most recent activity to the top of the resultset.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dineshrajan SSC Journeyman Points: 77 More actions · Answer 7

dineshrajan

SSC Journeyman

Points: 77

March 11, 2008 at 4:40 am

#787854

Very useful Query. Thanks a lot:)