ORDER BY vs Sort -- one column is NULL

[Jim R]

I have three columns -- Use | City | School

In cities where the school is just the name of the city, city is NULL.  In cities that have multiple schools or the name of the school isn't *just* the name of the city I fill in the city name, but the name of the city isn't always incorporated into the school name.

https://www.courtsideindiana.com/tag/mccutcheon/

On the right hand side of the page, it prints:

• Kokomo
• Logansport
• Marion
• Lafayette Jefferson
• McCutcheon
• Harrison

It should read:

• Harrison
• Kokomo
• Lafayette Jefferson
• Logansport
• Marion
• McCutcheon

(I have a trigger that determines when to print the city into the name and when not to.)

 

The data table columns look like this (I struck out the city names not being used in printing the school name)

Use | City | School

0 | NULL | Kokomo

0 | NULL |  Logansport

0 | NULL | Marion

1 | Lafayette | Jefferson

0 | Lafayette | McCutcheon

0 | West Lafayette | Harrison

 

The query is actually looking for the teams in the same Sectional (bsect) as the featured school -- in the linked case above, McCutcheon.

$query = "SELECT use,city,school from a_schools as s1
	LEFT JOIN a_schools as s2
	ON s1.bsect = s2.bsect
	WHERE '" . $query_school . "' = CONCAT(s1.city,' ',s1.school) || '" . $query_school . "' = s1.school
	ORDER BY concat(s2.city,s2.school) asc"; 

(The WHERE has to look for examples where it's just the school name OR examples where the city is incorporated in the school name.)

In a lot of instances, how I have this ordered works, which is why I have it that way, but this is definitely an example where it doesn't.  

 

Do I wait to sort after the query?

 

[benanamen]

4 minutes ago, Jim R said:

In cities where the school is just the name of the city, city is NULL.

IMO, you are just creating a problem mixing data. It doesn't matter if the school name is the same as the city. A city is a city, a school is a school.

[kicken]

Indeed, you should just be filling in both columns regardless of if they are the same or not.  It doesn't make sense to do otherwise.

You could use COALESCE to use whichever column is not null in your sorting.

ORDER BY COALESCE(s2.city, s2.school)

 

[Jim R]

26 minutes ago, benanamen said:

IMO, you are just creating a problem mixing data. It doesn't matter if the school name is the same as the city. A city is a city, a school is a school.

Yes it does matters.  It matters to my Users, and it matters to those who want to see their school represented as the school is named.  It matters to me. 

 

 

Edited January 4, 2020 by Jim R

[Barand]

Then store the school name as it is named and don't mess around with removing/not removing city names.

Use | City           | School
----+----------------+---------------------- 
0   | NULL           | Kokomo 
0   | NULL           | Logansport 
0   | NULL           | Marion 
1   | Lafayette      | Lafayette Jefferson 
0   | Lafayette      | McCutcheon 
0   | West Lafayette | Harrison
 

If you sort the above by school only you get the order you wanted

[Jim R]

15 minutes ago, kicken said:

Indeed, you should just be filling in both columns regardless of if they are the same or not.  It doesn't make sense to do otherwise.

You could use COALESCE to use whichever column is not null in your sorting.

ORDER BY COALESCE(s2.city, s2.school)

 

Filling both columns won't change anything.  The city name isn't always relevant to how the school is referred to, but I do use the city name in other areas.  

I'll look up coalesce.  Thanks.

[Jim R]

4 minutes ago, Barand said:

Then store the school name as it is named and don't mess around with removing/not removing city names.

Use | City           | School
----+----------------+---------------------- 
0   | NULL           | Kokomo 
0   | NULL           | Logansport 
0   | NULL           | Marion 
1   | Lafayette      | Lafayette Jefferson 
0   | Lafayette      | McCutcheon 
0   | West Lafayette | Harrison
 

If you sort the above by school only you get the order you wanted

There is another part of the site where I want them to be distinctly separate.  

[Barand]

17 minutes ago, Jim R said:

and it matters to those who want to see their school represented as the school is named

but not always, apparently ...

8 minutes ago, Jim R said:

There is another part of the site where I want them to be distinctly separate

Sounds like you schools have a "formal name" and a "nickname", like users might have.

Edited January 4, 2020 by Barand

[benanamen]

14 minutes ago, Jim R said:

Yes it does matters.  It matters to my Users, and it matters to those who want to see their school represented as the school is named.  It matters to me.

That makes no sense at all. If you are  displaying the data from the school name column why would it not be what the school is named?

[Jim R]

5 minutes ago, benanamen said:

That makes no sense at all. If you are  displaying the data from the school name column why would it not be what the school is named?

Because it's not.

 

Lafayette Jefferson goes by Lafayette Jefferson High School.  McCutcheon High School in Lafayette just goes by McCutcheon.  

Harrison High School in West Lafayette goes by Harrison High School.  There is also Harrison High School in Evansville. 

There are nine schools in Indiana with Central as their name or in their name.  There are other instances of this with other words--North, South, East, West, Christian, Catholic, Prep, etc.

 

[benanamen]

Based on the site link I would say what you are looking for is a "short name" for the schools. I would add a column to the DB to hold that data. So you have columns city, school_full_name, school_short_name

[Jim R]

20 minutes ago, Barand said:

but not always, apparently ...

There are parts of the site regular users don't get to see, and the city breakdown is helpful without it being repetitive. 

[Jim R]

10 minutes ago, benanamen said:

Based on the site link I would say what you are looking for is a "short name" for the schools. I would add a column to the DB to hold that data. So you have columns city, school_full_name, school_short_name

Can't I query it out and sort it on the backend rather than creating all that extra data input?  

Can't I sort an array?  That's what I was seeking initially, trying to get some syntax help.  

 

[benanamen]

19 minutes ago, Jim R said:

Can't I query it out and sort it on the backend rather than creating all that extra data input?  

Sure you could but you are going to need code gymnastics to cover every possible case. Not really optimum IMO. The "extra data input" is a one time insert and you are done with it. At that point, it is as simple as selecting the column in your query.

[Jim R]

4 minutes ago, benanamen said:

Sure you could but you are going to need code gymnastics to cover every possible case. Not really optimum IMO. The "extra data input" is a one time insert and you are done with it. At that point, it is as simple as selecting the column in your query.

I have no problem printing it out how I want it.  Asking for help on how to better order/sort it.  Order By likely isn't the answer, but creating an array and sorting the array might be.  Just not sure of the syntax.  

[Barand]

What you will need is an AI sort function (v7.7 ?) which intuitively knows how you want to process each schoolname in the data, If you don't have that you have same sort problems in PHP as you do with SQL.

[Jim R]

18 minutes ago, Barand said:

What you will need is an AI sort function (v7.7 ?) which intuitively knows how you want to process each schoolname in the data, If you don't have that you have same sort problems in PHP as you do with SQL.

OK...I'll likely relent and combine some city/school into school name.  Maybe create a trigger in the database.  (I guess I could do that.)

 

But check this one out:

https://www.courtsideindiana.com/tag/carmel/

Only Hamilton Southeastern has City filled.  The rest are NULL, but Zionsville shows up second.  WTF?

I checked for white spaces in the columns.  There appears to be nothing irregular about the data. 

[Barand]

Are you sure they are NULL and not some with empty strings or other whitespace?

[Barand]

Of course, you could store the city and the full school name, then using the technique we discussed in this forum a couple of weeks ago, remove the city from the school name when required.

I.E.

SELECT school
     , city
     , TRIM(REPLACE(school, COALESCE(city,''), '')) as short_school
FROM school;

+------------------------------+----------------+----------------------+
| school                       | city           | short_school         |
+------------------------------+----------------+----------------------+
| Bloomington North            | Bloomington    | North                |
| Columbus East                | Columbus       | East                 |
| Fort Wayne Bishop Dwenger    | Fort Wayne     | Bishop Dwenger       |
| Neil Armstrong High          | NULL           | Neil Armstrong High  |
| Davy Crockett School         | San Antonio    | Davy Crockett School |
| Kokomo                       | NULL           | Kokomo               |
| Logansport                   | NULL           | Logansport           |
| Marion                       | NULL           | Marion               |
| Jefferson                    | Lafayette      | Jefferson            |
| McCutcheon                   | Lafayette      | McCutcheon           |
| Harrison                     | West Lafayette | Harrison             |
| Carmel Greyhounds            | NULL           | Carmel Greyhounds    |
| Zionsville Eagles            | NULL           | Zionsville Eagles    |
| Fishers Tigers               | NULL           | Fishers Tigers       |
| Noblesville Millers          | NULL           | Noblesville Millers  |
| Westfield Shamrocks          | NULL           | Westfield Shamrocks  |
| Hamilton Southeastern Royals | Hamilton       | Southeastern Royals  |
+------------------------------+----------------+----------------------+

 

[Jim R]

9 hours ago, Barand said:

Are you sure they are NULL and not some with empty strings or other whitespace?

Definitely sure.  I have triple checked.  

[Barand]

Going back to your original query ...

$query = "SELECT use,city,school from a_schools as s1
    LEFT JOIN a_schools as s2
    ON s1.bsect = s2.bsect
    WHERE '" . $query_school . "' = CONCAT(s1.city,' ',s1.school) || '" . $query_school . "' = s1.school
    ORDER BY concat(s2.city,s2.school) asc";

why aren't you basing your search on IDs instead of names, especially as you have so many schools with almost identical names?

[Jim R]

1 hour ago, Barand said:

Going back to your original query ...

$query = "SELECT use,city,school from a_schools as s1
    LEFT JOIN a_schools as s2
    ON s1.bsect = s2.bsect
    WHERE '" . $query_school . "' = CONCAT(s1.city,' ',s1.school) || '" . $query_school . "' = s1.school
    ORDER BY concat(s2.city,s2.school) asc";

why aren't you basing your search on IDs instead of names, especially as you have so many schools with almost identical names?

Tags, which initially drive the query, are generated by WordPress via Posts, so at some point I still have to match tag name with school name, and since the tags aren't always schools--players too--I have to determine which it is before I can go forward.  

 

[Jim R]

For anyone else who comes across this issue here is the answer:
 

ORDER BY 
  (CASE
      WHEN s2.use IS NULL THEN s2.school
      ELSE s2.city
  END)";

 

[Jim R]

As always, I appreciate the input from others.  At its worst, it teaches me which questions to ask, which is always helpful.  

[Barand]

12 minutes ago, Jim R said:

WHEN s2.use IS NULL

So the "use" column has some significance to this problem!? That's news.

Anyway, glad you found a solution to the problem you created for yourself.