4

Possible Duplicate:
SQL Query to get latest price

I have a database containing stock price history. I want to select most recent prices for every stock that is listed. I know PostreSQL has a DISTINCT ON statement that would suit ideally here.

Table columns are name, closingPrice and date; name and date together form a unique index.

The easiest (and very uneffective) way is

SELECT * FROM stockPrices s
WHERE s.date =
(SELECT MAX(date) FROM stockPrices si WHERE si.name = s.name);

Much better approach I found is

SELECT *
FROM stockPrices s JOIN (
  SELECT name, MAX(date) AS date
  FROM stockPrices si
  GROUP BY name
) lastEntry ON s.name = lastEntry.name AND s.date = lastEntry.date;

What would be an efficient way to do this? What indexes should I create?

duplicate of:
SQL Query to get latest price

Community
  • 1
  • 1
skolima
  • 31,963
  • 27
  • 115
  • 151

1 Answers1

0

I think that your second approach is very efficient. What's its problem?

You have to add indexes to name and date.

Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
  • Well, you have to add an index if performance requires it. If it's ten stocks and a year's daily data, I wouldn't be concerned: MySQL is relatively good at table scanning. – Mike Woodhouse Sep 10 '08 at 09:36
  • If he had few data he wouldn't be asking for an efficient way to do this, no? Even the first, obvious approach would be enough. – Vinko Vrsalovic Sep 10 '08 at 10:36