Hi,

how can i sort my query results by the number of duplicate field ?

for example my result is like this :

value3
value2
value7
value3
value5
value3
value7

value3 is repeated most, so it must show those 3 first, then value 7 ...

value3
value3
value3
value7
value7
value2
value5

something like above, is it possible?

Here's a simple version that should be easy to understand and get right.

Read each value and:
- add 1 to the count for that value
- if the new count for that value is greater than your overall max count, then set your overall max count to the new count for that value

You have now counted how many times each value occurs, and you know the maximum count of any of the values. Now you need to print the results:

In a for loop, iterate your test count from the overall max count down to 1:
- loop through your list of value counts:
- - if a value count matches your test count, then print the value

Yes, but Darin, the OP is asking how to sort SQL results by count of some field. I don't know how to do this (or if it's possible), but at least we could vastly improve on your version by getting SQL to do the counting.

You need to use GROUP BY [the *field* you want to count], and then ORDER BY count(*field*). This gives you the counts for each field value, so you could then loop through taking the values in order.

On another hand, probably the time it would take to find an SQL guru to give you the magic one-line program, it's probably more efficient just to do something like the above.

@Darin
I suppose you mean to do all by a server-side language, then I mean order it by MySQL line.

@Brian
The problem with group by is, it doesn't show repeated value rows, it will be like this :

value3
value7
value2
value5

I need to have all the rows. (Repeated values)

No, right. So you need something like:

SELECT fld FROM table GROUP BY fld ORDER BY count(fld) DESC

Then get the values from this search one by one as 'hitval' (say), and run a second DB call

SELECT * FROM table WHERE fld = 'hitval'

This will give you a batch of results - repeat until done.

I expect there is a smart way to feed the result of the count() operation into the basic access call, but I don't know what it is.

Here's the solution : (maybe not the best one)

first query :


now putting the result together for the second query :


and cut the end of it.

final query :



Cheers!

Thanks for taking time.
I learned something new

Right. So you need something like:

SELECT fld FROM table GROUP BY fld ORDER BY count(fld) DESC

Then get the values from this search one by one as 'hitval' (say), and run a second DB call

SELECT * FROM table WHERE fld = 'hitval'

This will give you a batch of results - repeat until done.

I expect there is a smart way to feed the result of the count() operation into the basic access call, but I don't know what it is.

Hi,

You can't do this in a single query as such, but you can use a correlated subquery:

SELECT val FROM vals;
val
1
6
6
6
6
2
2
2
3
3

SELECT val FROM vals v1 ORDER BY (SELECT COUNT(1) FROM vals v2 where v1.val = v2.val GROUP BY val) DESC;

val
6
6
6
6
2
2
2
3
3
1

Each value from the outer query will be matched to its corresponding count in the inner query