Order by for similar records

Databases
1

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

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

I am not sure why you do not JOIN the tables as follows:


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

with a subquery in the SELECT clause, it’s actually like a LEFT OUTER JOIN

5

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

If you want to order on the first column, put that column first in the ORDER BY clause

7

No i want ORDER BY project but projects should be by id: p.property_master_project

8

What?

9

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

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?


ORDER BY 
    project
  , p.id
11

Excellent done :slight_smile:

12

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?