CODEWITHSUNDEEP
sqlsql-server
sanjeev
sanjeev

Reputation: 97

Select column from one table order by column from another table

I have 2 tables:

Customer_Master:

 CUSTOMER_ID    CUSTOMER_NAME 
 ----------------------------
   1             Test1
   2             Test2

Ticket_Master:

 TICKETID   CUSTOMER_ID     UPDATEDATE
 --------------------------------------------------
    1           1           2017-03-03 00:00:00.000
    2           1           2017-03-20 20:09:31.000
    3           2           2017-03-20 20:11:00.000
    4           2           2017-03-20 20:15:29.000

I need results with all elements from Table 1 order by updatetime of Ticket_Mater. Join results in Duplicate rows whereas I need distinct rows from Customer_Master.

SELECT 
    a.CUSTOMER_ID, MAX(b.UPDATEDATE)
FROM 
    customer_master AS a 
INNER JOIN
    Ticket_master AS b ON a.CUSTOMER_ID = b.CUSTOMERID 
GROUP BY 
    a.CUSTOMER_ID 
ORDER BY 
    MAX(b.UPDATEDATE) DESC

Above query returns order by update but not all elements of customer_master.

SELECT  
    a.*, b.UPDATEDATE
FROM 
    customer_master AS a
INNER  JOIN 
    Ticket_master AS b ON a.customer_id = b.customerid
ORDER BY 
    b.UPDATEDATE desc

This Query Return Duplicate rows from Ticket_master.

Please help. Every Help will be highly Appreciated.

Upvotes: 2

Views: 65

Answers (2)

Vecchiasignora
Vecchiasignora

Reputation: 1315

Try this

SELECT distinct(a.*)
FROM customer_master AS a inner join
                      Ticket_master AS b ON a.CUSTOMER_ID = b.CUSTOMERID 
order by b.UPDATEDATE desc

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

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;

Upvotes: 2

Related Questions