0

This problem has been bugging me a lot since the past few days. Here's what I thought of:
A super big retail store company has stores in many different cities, each store was opened on a given day. The table tabulating the data would have (atleast) two columns,
(1) city name (2) date opened
Output desirable: multiple lists, each list containing all the stores of a given city ordered chronologically (by date opened) of 10 cities ordered alphabetically.
To get around this I was thinking of:
Fetch #1: Alphabetically fetching 10 cities from the table
Fetch #2 (multiple fetches in a loop): Iterating each fetched city, and obtaining
all of its respective stores, making the order by date opened.

The above method which I've come up with does the job, however I was wondering if there is a more concise way of doing it, which involves the looping though the stores of the cities while making fetch #1 itself, and thereby not increasing the time complexity by using an external loop and performing many sql executions instead of just one.
Would that be possible? one sql statement?

raj_n
  • 176
  • 2
  • 3
  • 11
  • [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables). – DCoder Oct 21 '12 at 06:47
  • 1
    @DCoder: The question only mentions one table... – eggyal Oct 21 '12 at 07:03

1 Answers1

0
SELECT * FROM my_table NATURAL JOIN (
  SELECT DISTINCT city FROM my_table ORDER BY city LIMIT 10
) t ORDER BY city, opened

Split into separate lists when looping over the resultset within your application: just inspect city to see if it has changed since the last iteration. In pseudocode:

for each record in resultset:
  if current city is not equal to last city then start new list
  output current record
  set last city to current city
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Hey thank you so much eggyal. Just, about the limiting the number of cities alphabetically while making the fetch, do you know how to implement that into your current solution? – raj_n Oct 21 '12 at 07:12
  • @Relfor: Which 10 cities do you want to choose? – eggyal Oct 21 '12 at 07:20
  • ten cities which are alphabetically ahead in the table – raj_n Oct 21 '12 at 07:24
  • @Relfor: One way would be to put `SELECT DISTINCT city FROM my_table ORDER BY city LIMIT 10` inside the parentheses above. – eggyal Oct 21 '12 at 07:26
  • thanks, it works without any limit set in the sub query, however it gives an error when limit is supplied in there, apparently mysql doesn't permit that. Anyway, thanks a lot for the help! – raj_n Oct 21 '12 at 07:38