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

[r937]

your query needs a bit of work

you have a comma in front of the FROM keyword which will give a syntax error

you have three tables but you join only two of them, consequently the results of that join are cross joined with each row of the third

what does "display it in the order defined in the sorted_people table" mean?

according to you, that table contains only ids

so they will be desiplayed in id sequence

[davidklonski]

you are correct about the extra comma.

The By_Lastname column in the sorted_people table contains a list of people IDs sorted by last name, e.g:

By_Lastname
3
2
5
1
4

This means that the correct order of people when sorted by last name is 3,2,5,1 and lastly 4

Now, some of the people may be considered private (if the privacy_table defines last_name to be privatized and the privacy attribute of a person is 'enabled').
Private people should be listed last.
Non-Private people (either last_name is NOT privatized in privacy_table or that person's privacy attribute is set to 'disabled') shoud maintain the sorting order as defined in the By_Lastname column in the sorted_people table

I hope this clarify things a bit

[r937]

This means that the correct order of people when sorted by last name is 3,2,5,1 and lastly 4

sorry to be the bearer of bad news, but if you sort by this id, you get 1,2,3,4,5

and if you don't sort by it, you get any old order whatsoever

there is no such thing as "sequence" within a relational table

if you need them in that order, add another column as sort sequence

sorted_people
sortseq By_Lastname
1000001 3
1000002 2
1000003 5
1000004 1
1000005 4

then ORDER BY sortseq

[davidklonski]

Here is what I was trying to do. Hopefully you could provide me with a tip on how this can be achieved.

I am storing data on people:
people:
ID int(5)
last_name varchar(255)
first_name varchar(255)
address varchar(255)

I need to be able to display the people sorted by either: last_name, first_name or adddress.
Since the content of the database cannot change (a system requirement) and I cannot rely on the database sorting mechanism (a system requirement), I pre-sorted all the people according to the different criterias and stored the sorting in a new table called: sorted_people.

sorted_people:
By_Lastname int(5)
By_Firstname int(5)
By_Address int(5)

The following is a possible instance of such table
By_Lastname: 2 1 5 4 3
By_Firstname: 3 4 5 1 2
By_Address: 3 2 5 4 1

Each column specifies the correct order the people need to be displayed for the appropriate attribute.

So basically, if the sorting is by last name, I would like to display the data on person 2, then person 1... lastly person 3 (as defined in the By_Lastname column).

Obviously there need to be a join with the people table as the actual data is stored there.

The last requirement says that I should maintain the sorting described above except for privatized people who need to be displayed last.
This is what I was trying to do with the where case clause inside the order by clause. I was trying to use the order that was defined in the sorted_people columns when the person is not privatized, and otherwise to display it last.

Can you tell me where I went wrong?
Is there a better solution for that? Assume that I must obtain the correct order from the database and I cannot sort in runtime (using order by)?

thanks in advance

[r937]

i don't understand the table

sorted_people:
By_Lastname int(5)
By_Firstname int(5)
By_Address int(5)

from the above definition, the rows in this table would be

2 3 3
1 4 2
5 5 5
4 1 4
3 2 1

how do you know who is who?

can you show these rows joined to a few sample people?

[davidklonski]

here is an example:

I have 3 people:

ID: 1
Last name: Smith
First name: John
Address: New York

ID: 2
Last name: Becker
First name: Tim
Address: Los Angeles

ID: 3
Last name: Turner
First name: Adam
Address: France

When sorted by last name the order of the people is: 2 (Becker), 1 (Smith), 3 (Turner)
When sorted by first name the order of the people is: 3 (Adam), 1 (John), 2 (Tim)
When sorted by address the order of the people is: 3 (France), 2 (Los Angeles), 1 (New York)

The numbers are the IDs of the people.

So when I need to sort by last name, I would like to access the sort_people table, get the appropriate order of IDs (in this case: 2,1,3) and use those IDs to display the data on each people in the correct order:

Becker, Tim - Los Angeles
Smith, John - New York
Turner, Adam - France

The reason I am not using Order By, is that sometimes I cannot rely on the way MySQL orders the attributes. For example, one of the attributes of a person could be his birth date. A requirement says that the birth date MUST be stored as a VARCHAR because a valid date could be: "Before 1945" or "On the second month of 1922".
Obviously I cannot rely on MySQL to sort by date with such a requirement so I thought I would sort it myself and store the sorted IDs in a table (sorted_people), and use that info to display the data sorted.

I hope this makes things a bit clearer.

[r937]

that part is totally clear -- the part about determining the sort sequence yourself

that is why you have to have a sort sequence field

for those three people, can you show the sorted_people rows

[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

[r937]

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.

i have been trying to tell you that i do understand the need for special handling but that your sorted_people table does not work

you say "here is what the table would look like"

but i have tried to tell you that this does not work

here's your sorted_people table shown not left to right but as rows:

23 9312
5234 5234
9312 23

okay, now, what are we supposed to make of that?

if you join the 3rd row to becker, what do you get?

you get becker, 9312, 23

what's that supposed to mean?

there is no such thing as position within a table

yes, becker is 1st by lastname, but how the heck do you divine this piece of information from becker, 9312, 23

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

no, sorry, it just does not work that way

you need a sort sequence field for that purpose

in other words, something perhaps like this:

sorted_people_by_lastname
2 23
3 5234
1 9312

see the sort sequence fields there? 9312 is 1st, 23 is 2nd, 5234 is 3rd, not by "position" within the column, but by the value of the sort sequence field

[davidklonski]

important:

WhenI use the following query:

select sorted_people.By_Lastname, people.Last_name
from people, sorted_people
where (sorted_people.By_Lastname = people.ID);

All but the privacy requirements are met.
People are displayed according to the sorting order as defined in the sorted_people.By_Lastname column.

However, when I try to add the condition saying to put privatized people last, the output seems to sorted randomly:

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

what is wrong with the last addition?

[r937]

the last addition is missing the join condition between the privacy_settings and the people table

and the reason it appears to sort in random order is because it is sorting by 1 or 2 only

you'll need an additional column in the ORDER BY after the 1/2 in order to sort them into your desired sequence

[davidklonski]

the last addition is missing the join condition between the privacy_settings and the people table

and the reason it appears to sort in random order is because it is sorting by 1 or 2 only

you'll need an additional column in the ORDER BY after the 1/2 in order to sort them into your desired sequence

Following your advice I have added an additional column called SequenceOrder to the sorted_people table.

This is my order by clause:
order by case when (privacy_settings.shouldPrivatizeLastName = 'no' OR people.Privacy = 'disabled') then SequenceOrder end;

I don't know what I need to add to this clause to cause it to display all the people who don't meet the case condition -> last.

I have read about conditionals in the Manual and this is what I have found:
CASE
WHEN search_condition THEN statement
[WHEN search_condition THEN statement ...]
[ELSE statement]
END CASE

I am not sure what statement should go in the else clause...

[r937]

order 
    by case when privacy_settings.shouldPrivatizeLastName = 'no' 
              or people.Privacy = 'disabled'
            then 0 else 1 end
     , SequenceOrder

[davidklonski]

order 
    by case when privacy_settings.shouldPrivatizeLastName = 'no' 
              or people.Privacy = 'disabled'
            then 0 else 1 end
     , SequenceOrder

thank you very much - you helped me a lot

can I ask you one last question regarding the syntax you used in the order by clause? I would like to learn from this experience so I won't return to the same mistakes...

what does it mean that you use 0 if the condition evaluates to true, and 1 otherwise. How does that affect the sorting?
and how does the ,SequenceOrder at the end relate to those numbers?

thanks again

[r937]

create table orderbydemo
( foo int
, bar int
, SequenceOrder int
);
insert into orderbydemo values
  ( 302, 402, 100012 )
, ( 703, 802, 100009 )
, ( 903, 801, 100006 )
, ( 303, 702, 100011 )
, ( 604, 903, 100012 )
, ( 101, 808, 100008 )
, ( 202, 303, 100004 )
, ( 306, 702, 100010 )
, ( 307, 802, 100013 )
, ( 206, 905, 100005 )
;

select foo
     , bar
     , case when foo between 300 and 399
              or bar = 303
            then 1 else 0 end    as sortfirst
     , SequenceOrder   
  from orderbydemo             
order 
    by case when foo between 300 and 399
              or bar = 303
            then 1 else 0 end
     , SequenceOrder     
     
foo,bar,sortfirst,SequenceOrder
206,905,0,100005
903,801,0,100006
101,808,0,100008
703,802,0,100009
604,903,0,100012
202,303,1,100004
306,702,1,100010
303,702,1,100011
302,402,1,100012
307,802,1,100013