Question 88040 in ASP.NET Question posted in
The official documentation can be found here.

Retrieving the last values from the database – should the results be sorted? – Asp.net

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:

ID (int, AI) VALUE DATE
1 2.5 20.01.2021
2 2.7 21.01.2021
3 2.6 22.01.2021

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?

Tags:

4

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

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

    Login or Signup to reply.
  4. 0

    You can do it in both ways.

    If you’re using SQL with Dapper or ADO.NET, then you can write a query.
    It should be sorted if you need the last 10 values

    SELECT TOP 10 AVG(your value) AS average
FROM YourCurrencyExchangeTable
ORDER BY DATE DESC

    If you’re using EntityFrameWorkCore, you can write

    var avg = db.yourDbContext
        .Where(c => c.Date >= tenDaysAgoDate)
        .Average(c => c.yourValue)

    I hope my answer helps 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search