CODEWITHSUNDEEP
sql
skydoor
skydoor

Reputation: 25868

Select the lastest N day data from 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!

Upvotes: 1

Views: 196

Answers (2)

CooL i3oY
CooL i3oY

Reputation: 800

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

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

Upvotes: 1

Related Questions