CODEWITHSUNDEEP
mysqlsql
user3223205
user3223205

Reputation:

SQL Query selecting from two separate tables using a order clause

I have two tables:

tickets
ticket_updates

and i am trying to run a query where it shows data from the tickets table and order by the latest datetime in the ticket_updates table

SELECT * from tickets where status = 'Completed' order by ??? DESC LIMIT 0,50

ticketnumber in the tickets table matches ticketnumber in the ticket_updates table

Upvotes: 1

Views: 38

Answers (3)

m00hk00h
m00hk00h

Reputation: 517

Join the two tables, then order by:

SELECT a.*
from tickets a, ticket_updates b
where a.ticketnumber = b.ticketnumber and
a.status = 'Completed'
order by b.YOUR_DATETIME_FIELD DESC LIMIT 0,50

Upvotes: 0

thebignoob
thebignoob

Reputation: 471

SELECT t.*
FROM tickets AS t
JOIN ticket_updates AS tu ON t.ticketnumber = tu.ticketnumber
WHERE status = 'Completed'
ORDER BY tu.datetime DESC LIMIT 50

Try this.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270703

One approach is to summarize the ticket_updates table to get what you want:

select t.*
from tickets t join
     (select ticketid, max(datetime) as max_datetime
      from ticket_updates
      group by ticketid
     ) tu
     on t.ticketid = tu.max_datetime
order by tu.max_datetime desc
limit 0, 50;

Upvotes: 0

Related Questions