The database stores the currency exchange rate on a given day. Each day, one currency exchange value is collected and stored in the database as:

If I would like to calculate the average exchange rate from the last 10 days, should I first sort the data by date and only retrieve the last 10 records when downloading the data, or is it enough to download the last 10 records from the database without sorting?

CodePudding user response：

You can simply do in SQL Server database

SELECT TOP 10 AVG(VALUE) AS AverageRate
FROM YourTable
ORDER BY Id DESC

Concept should be same in other relational databases.

CodePudding user response：

Tables (and table expressions such as views and CTEs) in SQL represent unordered sets. To get data in a particular order, you must specify an ORDER BY clause.

In fairly generic SQL you can do

SELECT AVG(VALUE) AS AverageRate
FROM (
    SELECT VALUE
    FROM YourTable AS t
    ORDER BY Id DESC
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
) AS t

In some RDBMSs, instead of OFFSET FETCH, you use either LIMIT or TOP to achieve the same effect. You still need ORDER BY.

CodePudding user response：

Basically you have to sort first ( on date) and then get the last 10 values, so you're on the right track.