Jump to content
#StayAtHome ×
Jim R

ORDER BY vs Sort -- one column is NULL

Recommended Posts

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?

 

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites

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)

 

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Jim R

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.  

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Barand

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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.  

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.  

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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  |
+------------------------------+----------------+----------------------+

 

  • Like 1

Share this post


Link to post
Share on other sites
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.  

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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.  

 

Share this post


Link to post
Share on other sites

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)";

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.