Ordering Groups of Rows by Single Row Criteria in MySQL?

Question

Suppose that I have a database table that contains information on cities across the United States. My display shows information first grouped by State, then County, and finally by City something like below:

---

• Alabama
  • Autauga County
  • city 1
  • city 2
  • etc
  • Baldwin County
  • city 1
  • city 2
  • etc
  • County etc
• Alaska
  • Alaska county 1
  • city 1
  • alaska county 2

The query would look something like:

select * from myInfo order by state, county, city

A new wrinkle is that all states that contain cities called 'Lincoln' should have their states and counties ordered first. States without cities named Lincoln are to be ordered after. The desired result is something like:

• Nebraska
  • Lancaster County
  • Lincoln
  • some other Lancaster City
  • Some other Nebraska County
• New Mexico
  • Lincoln County
  • Lincoln
  • some other city
  • some other county
• A Non-Lincoln State

I could do this complex ordering in code but I'm wondering how difficult it is to implement in pure sql. How difficult is the resulting query?

Answer 1

... implement in pure sql.

I see two possible ways.

1) add IIF() function on the COUNTY column and order by that function such as IIF(COUNTY="Lincoln County", 1, 2) -- you're using the IIF() to "override" the alphabetic ordering by replacing it with a "1" or "2".

2) create another table with fields (COUNTY_ID, COUNTY_NAME, SEQUENCE) that specifies the particular sequence you want. You then join to this table and add SEQUENCE to the ORDER BY clause

Option 2 requires knowing all your counties ahead of time. But the advantage is that you can add more complex sequencing rules that's easier to comprehend/maintain than nested IIF() clauses.

Answer 2

Another thing you could try is the following:

SELECT *, (city != 'Lincoln') AS test
FROM myInfo
....
ORDER BY test, state, county, city

The (city != 'Lincoln') will give you an additional column with value of 1 where the city name is Lincoln, and those rows will then get thrown to the top in the ORDER.

Answer 3

Assuming your database is not normalized, here's how I'd proceed: select everything from the table, add a self-join to see if the (state, county) pair has a city named 'Lincoln' and sort those first.

   SELECT t1.*, (t2.state IS NOT NULL) AS has_lincoln
     FROM myInfo t1
LEFT JOIN myInfo t2 ON (t2.state, t2.county, t2.city)
                     = (t1.state, t1.county, 'Lincoln')
 ORDER BY t1.state, has_lincoln DESC, t1.county, t1.city

If for some reason your database has multiple cities named 'Lincoln' with the same (state, county) pair, add DISTINCT to the mix. Or better yet, make sure you don't have duplicate rows in your database by adding a UNIQUE key on (state, county, city)