Select the lastest N day data from table

Question

Hi I have a table

price date        time
1.0   20100815      1
1.2   20100815      2 
1.3   20100815      3
2     20100814      1
3.1   20100813      1
3.2   20100813      2
:
:
:

Now I want to select the latest 3 days price with all the time, I use like this

select price, date from allquotes where date in 
      (select date from allquotes group by date order by date desc limit 3)

Is this right? Is this efficient? Any suggestion to improve this?

If I would like to show only one price with the latest time, how to do that?

Thanks so much!

Answer 1

This should do the trick on SQL Server:

select top 3 q.pricee, q.date, q.time
from (
    select date, max(time) as MaxTime
    from allquotes
    group by date
) qm
inner join quotes q on qm.date = q.date and qm.MaxTime = time
order by date desc

For MySQL, try:

select q.pricee, q.date, q.time
from (
    select date, max(time) as MaxTime
    from allquotes
    group by date
) qm
inner join quotes q on qm.date = q.date and qm.MaxTime = time
order by date desc
limit 3

Answer 2

Select Top 3 Sum(price),
             date,
             Sum(time)
From   allquotes
Group By date
Order By date Desc