Jump to content

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?

 

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

 

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

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.

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.  

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

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.

 

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.  

 

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.

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.  

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. 

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

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.  

 

Link to post
Share on other sites
This thread is more than a year old.

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.