How to use a sub query

[pcsmurf]

I have a table of contracts and a table of prices. For the purpose of this test the contracts are just contractid and name. The prices table contain records in the format: contractid, AsAt, Price.

prices.contract and prices.AsAT make up the primary key so each contract will have multiple records in the price table but only ever 1 price for any given day.

What I am trying to do is produce a recordset showing the latest date a contract has a price for.

The SQL :

SELECT contract, AsAt
FROM arc_pri_prices
WHERE contract = 1
ORDER BY AsAT DESC
LIMIT 0,1

will give me the latest date for a given contract but I need this for every contract. I'm thinking sub queries is the answer but I seem to be going in circles. Can anyone point me in the right direction please.

 

[feherke]

Hi

Use [tt]group by[/tt] clause :

[gray]-- just contact and max date[/gray]
[b]select[/b]
contact[teal],[/teal]max[teal]([/teal]AsAt[teal])[/teal] [b]as[/b] AsAt

[b]from[/b] arc_pri_prices

[b]group[/b] [b]by[/b] contact[teal];[/teal]

[gray]-- contact, max date and price on max date[/gray]
[b]select[/b]
[teal]*[/teal]

[b]from[/b] arc_pri_prices

[b]where[/b] [teal]([/teal]contact[teal],[/teal]AsAt[teal])[/teal] [b]in[/b] [teal]([/teal]
  [b]select[/b]
  contact[teal],[/teal]max[teal]([/teal]AsAt[teal])[/teal]

  [b]from[/b] arc_pri_prices

  [b]group[/b] [b]by[/b] contact
[teal])[/teal][teal];[/teal]

[gray]-- contact, max date and price on max date ( faster )[/gray]
[b]select[/b]
app[teal].*[/teal]

[b]from[/b] arc_pri_prices app
[b]inner[/b] [b]join[/b] [teal]([/teal]
  [b]select[/b]
  contact[teal],[/teal]max[teal]([/teal]AsAt[teal])[/teal] [b]as[/b] AsAt

  [b]from[/b] arc_pri_prices

  [b]group[/b] [b]by[/b] contact
[teal])[/teal] foo [b]using[/b] [teal]([/teal]contact[teal],[/teal]AsAt[teal])[/teal][teal];[/teal]

Feherke.

http://feherke.github.com/

 

[pcsmurf]

Thanks