order by same rows number?

[asmith]

Hi,

 

Is there a way that I could order the result coming from database by the number of value is repeated?

 

for example if in the result (coming from while loop and mysql_fetch_array) I have 3 rows that "name" field is "john" ,  2 rows that "name" field is "tom" , and 5 rows that "name" field is "jack" .

 

Is there a way I could order these like it give me the  5 "jack" first (it have 5 rows) m then it give me "john" (with 3 rows), then "tom"  ?  (or asc maybe)

 

Thanks 

 

(like order by max rows ...)

[zenag]

select * from tablename order by name   ....will list out name fields in order of name field 

[asmith]

sorry sir but you didn't get the point.

 

your code will order by name, like by ordering by first letter.  I want to order same value by "how many rows it is in there.

 

I want this :

value1

value2

value1

value4

value4

value1

value5

value5

value5

value1

 

to become :

 

value1

value1

value1

value1

value5

value5

value5

value4

value4

value2

 

you see?  number of rows the value is in, value1 is repeated the most (4 times) so first, value5 is repeated 3 times, value4 is repeated 2 times  and ...

 

 

[zenag]

here s the code what u expect sir..

SELECT login.*,count(*)as count FROM `login` group by user having count(user)>1 order by count(*) desc

[zenag]

i ve used login as my tablename..use fieldname which has duplicate entries...

SELECT tablename.*,count(*)as count FROM `tablename` group by fieldname having count(fieldname)>1 order by count(*) desc

[asmith]

Thanks for the reply, Still your code is not working

 

your code output is this :

 

value1

value5

value4

value2

 

it shows one row of each value.

 

[fenway]

You CANNOT use * with group by.

[asmith]

fenway I didn't exactly get it.

 

when using group by it removes duplicated values.

 

How to write this query  ?

the main thing is , I have a table called "labels" .  and another table "members" . in members table there's a field named "label" . and in label table there's a field "label_name"  . my query is this :

 

select labels.*,members.* from labels inner join members on labels.label_name=members.label

 

so I want to have the results like the label which has the most people comes first and then other ...

I'm counting the number of members of each label with php. so I need all rows of a label name come after each other  :

label_name  member      label

label4          member1    label4

label4          member4    label4

label4          member7    label4

label5          member3    label5

label5          member8    label5

label3          member2    label3

 

 

hope its clear enough.

[fenway]

You can only retrieve the value you used to aggregate the COUNT()... in this case, "fieldname"... which means that if you want the other value for the "top" record, you'll need to join this back to the original table.