Getting second result in the set

[pcsmurf]

I have a table called prices which stores prices for different contracts. Sample shown below.

id AsAt contract price
1 01/01/2012 1 6.5
2 02/01/2012 1 7
3 04/01/2012 1 5.2
4 04/01/2012 2 6
5 03/01/2012 2 9.1
6 01/02/2012 3 7
7 02/02/2012 3 6
8 03/02/2012 3 5.1
9 01/01/2012 4 6.5

I need to generate a resultset that shows the second to last date a price is quoted for. The final resultset would be:

contract penultimatepricedate
1 2/1/2012
2 3/1/2012
3 2/2/2012
4 1/1/2012

I can use the query

select contract,AsAt
from test.test_prices
where contract = 2
limit 1,1

To get the information for an individual contract but I can't seem to get the complete list (group by ???)

 

[feherke]

Hi

According to the sample result, this sentence needs the amendment in red :

I need to generate a resultset that shows the second to last date [red]or the last one if is the only[/red] a price is quoted for.

[b]select[/b]
contact[teal],[/teal]max[teal]([/teal]p[teal].[/teal]asat[teal])[/teal]

[b]from[/b] test_prices p
[b]left[/b] [b]join[/b] [teal]([/teal]
  [b]select[/b]
  contact[teal],[/teal]max[teal]([/teal]asat[teal])[/teal] asat[teal],[/teal]count[teal](*)=[/teal][purple]1[/purple] only

  [b]from[/b] test_prices

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

[b]where[/b] p[teal].[/teal]asat[teal]<[/teal]foo[teal].[/teal]asat
[b]or[/b] foo[teal].[/teal]only

[b]group[/b] [b]by[/b] contact

Feherke.

http://feherke.github.com/