Select top 3 entries from SQL query

[PDT]

If data sorted is called by PHP into array from 1 column based on the value fom another column in MySQL, how could you select (in the query) only the, say 3, most frequently reported entries only and discard the rest from your array?

Any help appreciated

 

[KarveR]

if one of the columns is a total of the times the value is returned, sort the data descending and then limit 3;

name - score
apple - 10
orange - 9
pear - 4
banana - 2

select * from fruits order by score desc limit 3;

this would return the top 3in the order they appear above, based on the number in score. <hr>
There's no present like the time, they say. - Henry's Cat.

 

[PDT]

Unfortunately there is no column the frequency refers to because the database updated too regularly, gets ~200-400 entries per week and is already established.

I wanted more a PHP-based limiting command in the query so of the 10-20 things that are returned for a query string, only say 5 of the most important entries are parsed into the array for viewing,

Thanks for thoughts though,


P

 

[KarveR]

If your select returns 20 items, how do you decide which of them are most relevant and therefore should be included in the final cut? <hr>
There's no present like the time, they say. - Henry's Cat.

 

[PDT]

This database is a medical related site, health professionals report genetic diseases encountered over time in a variety of hospitals around Australia, for each day (1st column), users can report a variety of diseases (2nd column) from a tiered menu. What I wanted to do was to report the 5 most frequent diseases encountered eg. in each demographics area.

ie. for a specific region/area in Australia, the most frequent 5 diseases reported. I am having trouble narrowing down this selection from the multitude of low frequency entries around the major ones, ie. the 5 most frequent of say 20 possible diseases.

SHould I instead try to index them, this is a daunting task as the database is slready underway and reasonably established, but it my employer wants this...

Cheers,


P

 

[KarveR]

Adding extra fields to an established db is not a troublesome job at all, I have just done this to a database with nearing 800,000 records.

What fields do you have, a date and a disease?
any other fields?



<hr>
There's no present like the time, they say. - Henry's Cat.

 

[piti]

i think he wants something like this

select desease, count(*) as desease_count from table
group by desease
order by desease_count desc
limit 5

this should select desease name, how many times is the desease entered (desease_count) and it returns only the 5 topmost records

if you want to add some aditional conditions - you will probably need subselects or temporary tables to get it work

hope this helps

 

[PDT]

Thanks alot piti, will give this a go tomorrow when I get home - looks promising,

All columns for values entered by user: date, disease, gender, age_diagnosed, body system, user and misc_comments