CODEWITHSUNDEEP
sqlt-sql
Nikoli
Nikoli

Reputation: 167

Last Sale for a given Machine

I am trying to find the last sale for given device.

I have 2 tables

  1. Machines
  2. Sales.

They are join via Machines.MahineID = Sales.MachineID

Here is what I have so far :

Select Machines.Name, Sales.DateOfSale from Machines 
Left Outer join Sales on Sales.MachineId = Machines.MachineId

This gives me all sales for a given machine however I would like just the latest sale for a given machine. I would also like to know if a machine has never made a sale. Hence the Left outer join.

Upvotes: 2

Views: 63

Answers (2)

Aprillion
Aprillion

Reputation: 22304

if you need name and last sale only:

select Machines.Name, max(Sales.DateOfSale)
from Machines 
left join Sales on Sales.MachineId = Machines.MachineId
group by Machines.Name

if you need other columns from this last record:

select Machines.Name, Sales.*
from Machines 
left join (
  select MachineId, max(DateOfSale) max_DateOfSale
  from Sales
  group by MachineId
) max_Sales
  on max_Sales.MachineId = Machines.MachineId
left join Sales
  on Sales.MachineId = Machines.MachineId
  and Sales.DateOfSale = max_Sales.max_DateOfSale

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can try this:

Select Machines.Name, Sales.DateOfSale
from Machines 
Left Outer join Sales s on Sales.MachineId = Machines.MachineId
where not exists (
    select *
    from Sales os                      -- Other sale...
    where s.MachineId = os.MachineId   -- Same machine
      AND os.DateOfSale > s.DateOfSale -- later date
)

There is a correlated exists query that checks if there are other sales on the same machine that happened at a later date.

Upvotes: 1

Related Questions