Best way to get results - Sort by 2 columns, one in ascending and other in descending

Data structure :
{
"id": "2919220",
"country_code": "US",
"ascii_city_name": "downey",
"city_name": "Downey",
"region": "CA",
"population": "111862",
"latitude": "33.9400000",
"longitude": "-118.1316667",
"region_name": "California",
"country_name": "United States"
}

need help for following query : Search All cities starting wihl word "Dall" and order the results by population field Descending and finally give me only the top 10 results.

In standard MySQL I could do :
SELECT * from cities where ascii_city_name LIKE '%Dall' ORDER BY population DESC,ascii_city_name ASC LIMIT 0,10

for ordering by city name ASC or DESC, I have got a view like this already which works great.

function (doc, meta) {
emit(doc.ascii_city_name,[doc.region_name,doc.country_name,doc.population]);
}

Unable to figure out how to go about from here. From what I can guess, I could write the MAP like this ? )
function (doc, meta) {
emit([parseInt(doc.population),doc.ascii_city_name],[doc.region_name,doc.country_name]);
}

That gives me the top 10 populated queries as required in Descending Order of population
and then query the key by descending, which would give me oreded by population descending, However, then how to send the start key to filter out the results matching city name startign with "Dall" .

Please provide a solution for this problem, since this is proabably typically a starting simple query hitting every development cycyle daily.

To Repeat, here is the exact requirement.

Select top 10 cities meeting all of the following criteria :

1) City Name Must start with word "Dall"
2) get the top 10 most poulated cities meeting criteria 1)
3) Finally from the results from 2) - Get the list of cities ordered by City Name (the population sort does not matter now at this step since we already have the top 10 populated entries, but now for presentation purposes need to sort the results by city name).

The SQL query I posted actually also would not work 100% too. Would probably need subqueries.

However, how to achieve the 3 points above using the data structure mentioned above. Woul dlike to avoid application level filtering/sorting.

0 Answers

No answers yet