...

View Full Version : Sorting Resuts



utstrangelove
05-10-2011, 03:30 PM
Hi all, i'm trying to figure out how to display the results of people so that the names are next to each other, which works great simply by using a simple select statement and ordering it by name, but to complicate matters I also want to retain the id order, 1,2,3 etc. example below

id name
1 Luke
2 Rachel
3 Craig
4 Luke

desired results im looking for,

id name
1 Luke
4 Luke
2 Rachel
3 Craig
5 Sarah
6 John

Would I need to sort the result first and then pass them to a temporary table for further sorting?


Thanks for looking.

guelphdad
05-10-2011, 03:49 PM
you can use an ORDER BY clause on more than one name



SELECT
id,
name
FROM
yourtablename
ORDER BY
name,
id

utstrangelove
05-10-2011, 04:27 PM
Thanks for the response but that doesn't do what i'm looking for unfortunately.

Old Pedant
05-10-2011, 07:15 PM
So you want to have them in order of the *LOWEST* number for each person, right?



SELECT P1.*, P2.minid
FROM people AS P1,
( SELECT MIN(id) AS minid, name FROM people GROUP BY name ) AS P2
WHERE P1.name = P2.name
ORDER BY P2.minid

But fair warning: If you have two unrelated people with the same name, this will lump them together.

It's an okay solution for a small table, but if you had 100,000 people in your table, the likelihood is you would have some duplicated names that are not the same person.

If you have a *DIFFERENT* field that is more likely to be unique than is name, it would be better to use it instead of name. Or in addition to name.

For example, phone number. Joe Brown and Mary Brown might have the same phone number, so you can't use the phone number alone, but...


SELECT P1.*, P2.minid
FROM people AS P1,
( SELECT MIN(id) AS minid, name, phone FROM people GROUP BY name, phone ) AS P2
WHERE P1.name = P2.name AND P1.phone = P2.phone
ORDER BY P2.minid


Or something along those lines.

utstrangelove
05-11-2011, 08:56 AM
Thanks old pendant I appreciate you trying to help with my problem.

Just to confirm (using higher id numbers) if a persons has 2 usersname that are not next to each other them put them next to each other and also try and retain as much a the order of the id as much as possible.

so 1,2,3,4,5,7,6,8,9,10,13,11,12,14 as an example

id username
230 adam123
231 jamesXYZ <-
232 steve123
233 jamesXYZ <-
234 luke123abc
235 luke123abc
236 rachel789


id user_id
230 adam123
231 jamesXYZ <-
233 jamesXYZ <-
232 steve123
234 luke123abc
235 luke123abc
236 rachel789

Thanks for you time and energy

Old Pedant
05-11-2011, 08:45 PM
Yes, I didn't think to make sure that the grouped names were still in numerical order, but that's easy: Just add to the ORDER BY:


SELECT P1.*, P2.minid
FROM people AS P1,
( SELECT MIN(id) AS minid, name FROM people GROUP BY name ) AS P2
WHERE P1.name = P2.name
ORDER BY P2.minid, P1.id

*probably* they would come out in that order, anyway, if id is the primary key field of the table, but if you put it in the ORDER BY it will be forced to come out that way.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum