is it possible ?

[sayfrndship]

Hi

Please tell me how to do following

I have table comp_detailproduct
In which there is productID field, Product ID is taken from other table containg products.

there can be more than one productID in details table, i have to use query, that will reutrn result, with most occurence of product id first, for exp is Product ID 1 is repeated 4 time, product id 2 is repeated 3 time, i need to use query that will return 1 first, no matter where is located 1 either it's on last rows.

[chazzy]

did you try using an ORDER BY clause?

[sayfrndship]

Not yet , but i think order by is used for ordering either ascending or descending,
let suppose i have 3 number of record of value a, and 4 number of record of c and 5 number of record of b, now i need to have b in first , then c next down then a next last. And this is not possible via order By, Dont u think ?

[AlaNio]

Maybe using a GROUP BY ? or perhaps using COUNT to count the number of occurances then order it by the results of that .

[Phill Pafford]

Use both ORDER BY and GROUP BY


SELECT field_1, field_2, COUNT(*) AS TALLY
FROM table
WHERE conditions
GROUP BY field_1
ORDER BY TALLY DESC


A query would be good to see as well

[sayfrndship]

Thnx phill and alanio suggestions are right, but i have already used this logic thnx again.

[Phill Pafford]

After looking at your first post why cant you just ORDER by the Product ID if this is numeric? ASC?

When you say "Product ID 1 is repeated 4 time" is this the same data for each record? if so use DISTINCT.

A query would be helpful as well