Sql Order by a Column from Another Table

SQL order by a column from another table

Join to the people table and then order by the field that you want.

SELECT
  m.* 
FROM 
  "memberships" AS m
  JOIN "people" AS p on p.personid = m.personID
WHERE
  m.groupId = 32
ORDER BY 
  p.name

ORDER BY column FROM another table

You should have a foreign key on friends table for user.
For eg say it userid.
Then above query will be:

SELECT m.* 
FROM users AS m 
    JOIN friends AS p ON p.userid = m.id  
WHERE m.username = '$username' 
ORDER BY p.last_act DESC

Sort by column from another table

You seem to be looking for a join:

select p.name, b.title, b.year_published
from books b
inner join publishers p on p.publisherID = b.publisherID
order by p.name, b.title

Ordering SQL results by attributes in another table

One thing you can do is to put the inner query into ORDER BY. This will keep the outer query "clean" as it will only select from buses. This way you won't need to return any additional fields.

SELECT buses.*
FROM buses
ORDER BY (
  SELECT MIN(stops.time) FROM stops WHERE stops.bus_id = buses.id
)

MYSQL Order from another Table

There are 2 ways to sort. Ascending order and Descending order. You have not mentioned the order. So I am providing you both answers with 2 variations:

ASCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.product_id ASC, table2.volgnr ASC;

DESCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.product_id DESC, table2.volgnr DESC;

If you want to tell MySQL to first sort FIRST by volgnr and then by product_id:

ASCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.volgnr ASC, table2.product_id ASC;

DESCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.volgnr DESC, table2.product_id DESC;

Hope that helps.

Edit 1:

I have now edited the query so that it does not give you duplicates in results. Try it out and let me know how that goes.

Edit 2:
Added Group By clause. Try this out.

Select column from one table order by column from another table

As there can be several entries in Ticket_Master for a CUSTOMER_ID you must decide by which of the possible dates to sort. This would usually be the first or the latest date per CUSTOMER_ID.

You can do this with a subquery:

select *
from customer_master cm
order by
(
  select max(tm.updatedate)
  from ticket_master tm
  where tm.customer_id = cm.customer_id
) desc;

---

Related Topics