SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by for similar records

    I have 2 tables one table named as projects and project details, projects is the master table of project details and i am getting records by subquery as:

    SELECT id, (select project_name from project_details where id=project.projectID) as property_master_project, filed2,field3 FROM projects WHERE ORDER BY property_master_project asc



    id=354, property_master_project=Arabian Ranches
    id=355, property_master_project=Arabian Ranches
    id=361, property_master_project=Arabian Ranches
    id=459, property_master_project=Arabian Ranches
    id=467, property_master_project=Arabian Ranches

    I want to know how order by is working when project names are same (ORDER BY property_master_project asc ) as i want all projects sequence by ids so that it should show as:


    id=354, property_master_project=Arabian Ranches
    id=355, property_master_project=Arabian Ranches
    id=359, property_master_project=Arabian Ranches
    id=461, property_master_project=Arabian Ranches
    id=467, property_master_project=Arabian Ranches


    I have tried as
    ORDER BY property_master_project, id asc
    ORDER BY property_master_project asc, id asc

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The query you posted is full of errors, so I'm sure it's not the one that is giving you those results.
    And the results you posted are already ordered by id.

    Both ORDER BY statements are ok, but there is actually no need to define ASC at all, because that is the default order.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am not sure why you do not JOIN the tables as follows:

    Code:
    SELECT p.id, project_name, p.field2, p.field3 
    FROM projects p
    INNER JOIN project_details pd ON (p.id = pd.projectID)
    ORDER BY project_name, p.id;

  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 rcashell View Post
    I am not sure why you do not JOIN the tables as follows:
    with a subquery in the SELECT clause, it's actually like a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tables i have used now: property and master_project

    SELECT p.id, p.property_master_project, project, property_address FROM property p INNER JOIN master_project pd ON (p.property_master_project=pd.id) WHERE p.listing_status!='Deleted' AND p.property_ref!='' AND p.property_manager1 IN(1)
    ORDER BY project, p.property_master_project


    Results i got:
    459 1 Arabian Ranches Al Reem 1, FR8888, 0909090
    361 1 Arabian Ranches Al Reem 1, 222, V34
    354 1 Arabian Ranches Al Reem 1, 43545, V45435
    467 1 Arabian Ranches Al Reem 1, 333, 34343
    355 1 Arabian Ranches Alvorada 1, 4545, 4545

    Still not the results i want. How can i get as:


    354 1 Arabian Ranches Al Reem 1, 43545, V45435
    355 1 Arabian Ranches Alvorada 1, 4545, 4545
    361 1 Arabian Ranches Al Reem 1, 222, V34
    459 1 Arabian Ranches Al Reem 1, FR8888, 0909090
    467 1 Arabian Ranches Al Reem 1, 333, 34343

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you want to order on the first column, put that column first in the ORDER BY clause

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No i want ORDER BY project but projects should be by id: p.property_master_project

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What?

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want as:

    354 1 Arabian Ranches
    355 1 Arabian Ranches
    361 1 Arabian Ranches
    459 1 Arabian Ranches
    467 1 Arabian Ranches

    Here you can see all projects are Arabian Ranches where as i want all Arabian Ranches order by id.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I love it how your test data keeps changing every time you post.

    Anyway, I assume those three columns are the first three of the SELECT of your query. You want the results ordered by the 3rd column, and then by the 1st?
    Code:
    ORDER BY 
        project
      , p.id

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent done

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have made finally full query now having 2 INNER JOIN and one sub query which works quite fine but it takes longer than normal:

    SELECT p.id, mp.project as property_master_project, pp.project as property_project, p.property_address, p.property_type, p.property_bedrooms, p.property_asking_price, (select employee_code from users where id=p.property_manager1) as property_manager1, p.market_appraisal_ref, p.property_ref, p.exclusive_listing, p.which_type FROM property p INNER JOIN master_project mp ON (p.property_master_project=mp.id) INNER JOIN project pp ON (p.property_project=pp.id) WHERE property_manager1 IN(1) AND listing_status!='Deleted' AND property_ref!='' ORDER BY mp.project, p.id

    explain query returns me:

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY p ref cmbd,arrow arrow 4 const 121 Using where; Using temporary; Using filesort
    1 PRIMARY mp eq_ref PRIMARY PRIMARY 4 crm.p.property_master_project 1
    1 PRIMARY pp eq_ref PRIMARY PRIMARY 4 crm.p.property_project 1
    2 DEPENDENT SUBQUERY users eq_ref PRIMARY PRIMARY 4 crm.p.property_manager1 1


    arrow is the index on columns: property_manager1, listing_status and property_ref

    Can i optimize further its index or query itself?


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
  •