selection output is sorted incorrectly

davidklonski

Hello

I am trying to display data on people sorted by last name.
Private people should be displayed at the bottom of the list.
For some reason, the data is displayed out of order.

Here are my tables:

People: # keeps info on people
ID int(5)
Last_name varchar(255)
Privacy varchar (255) # either 'enabled' or 'enabled'

sorted_people: # holds the Ids of the people sorted according to different critirias (lastname...)
By_Lastname: int(5)

The By_Lastname column holds the IDs of all the people, already sorted by lastname

privacy_settings: # what data should be private
shouldPrivatizeLastName varchar(255) # 'yes' or 'no'

I would like to display all the IDs and last names of the people sorted like so:
If the last name is not private, display it in the order defined in the sorted_people table. Otherwise, display it last.

Here is my query:
select sorted_people.By_Lastname, people.Last_name,
from people, privacy_settings, sorted_people
where (sorted_people.By_Lastname = people.ID)
order by case when (privacy_settings.shouldPrivatizeLastName = 'no' OR people.Privacy = 'disabled') then 1 else 2 end;

I am using (sorted_people.By_Lastname = people.ID) to connect between the two tables
I am using when (privacy_settings.shouldPrivatizeLastName = 'no' OR people.Privacy = 'disabled') then 1 else 2
to place all the people at the top or at the bottom of the list.

does someone see what causes the list not to be sorted according to what I am trying to achieve?

thanks in advance

leatherback

Probably a stupid question, but.. Why don't you use the sort option in the mysql query? Use group by privacy in the statement, and you should be set?

davidklonski

Let me clarify what I am trying to do, as I realize I wasn't clear in the previous message. Hopefully someone could tell me what I am doing wrong, or how to do it differently

I have 3 people:
ID: 5234
Last name: "Smith"
Birth Date: "4/3/70"
Privacy: 'enabled'

ID: 9312
Last name: "Becker"
Birth Date: "before 1970"
Privacy: 'disabled'

ID: 23
Last name: "Davidson"
Birth Date: "March 1966"
Privacy: 'disabled'

Note that the birth date attribute must be a varchar, as it may contain textual dates.
Because I cannot count on MySQL to correctly sort people by date (system requirement), and because the data doesn't change in runtime (system requirement), I thought I would pre-sort all the data myself and just store the sorted IDs in a separate table, called: sorted_people.
Here is what the table would look like for such data:

sorted_people:
By_Lastname: 9312, 23, 5234
By_Birthdate: 23, 9312, 5234

The order of the IDs in each column defines the correct order of the people when sorted in that criteria.

The last complication comes from the definition of privatization:
Each person can be either privatized or not (defined by the Privacy attribute).
There is also a system-wise table which defines what attributes are to be privatized.

for example:

privacy_settings:
shouldPrivatizeLastName = 'no'
shouldPrivatizeBirthdate = 'yes'

In order for a person to be privatized on last name (for example) his privacy_level should be set to 'enabled' AND the system shouldPrivatizeLastName attribute should be set to 'yes'

privatized people should be displayed last. Non-privatized people should be displayed according to the order defined in the appropriate column in the sorted_people table.

please help