I have a webapp..
A User clicks on a US State on a map, the State represents a two digit abbreviation of the state they clicked. In the database there are 1000's of locations each one has a two digit state abriviation that indicates what state they are from.
It is easy to return a list of locations by state
(Select * from Database where State = "Clicked State" Order By Whatever)
What I need is more complicated than that...each location entry has 10 columns (Product1 to Product10) and if that location has the product in stock, it has a "Y" in that field.
I need to return a list ordered by which location has the most "Y". So the users would be shown who has the most products in stock.
Database Table: Locations
Company, Address, State, Zip, Product1, Product2, Product3, Product4, Product5, Product6, Product7, Product8, Product9, Product10, Product10
How can I do this via a select SQL Statement?