SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selection output is sorted incorrectly

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by davidklonski
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by davidklonski
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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
    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...

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    order 
        by case when privacy_settings.shouldPrivatizeLastName = 'no' 
                  or people.Privacy = 'disabled'
                then 0 else 1 end
         , SequenceOrder
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •