0

I want to find the row number within the latest hour with the latest max value in a C# DataTable with the following columns: Name Score UnixTS. But can't seem to get the query right. I can easily get the row number for the latest max value from all values or as long as one hour has not yet passed. But as soon as one hour has passed the row number is null if there is an earlier max.

I have tried:

query = "UnixTS > " + fromUnixTS + " AND [Score] = MAX([Score])";
theRow = scoreTable.Select(query, "UnixTS DESC");

or

query = "[Score] = MAX([Score]) where UnixTS > fromUnixTS";

where fromUnixTS is now - 3600. Or even a SQL like query:

query = "SELECT * MAX([Score]) FROM scoreTable WHERE UnixTS > fromUnixTS ORDER BY UnixTS LIMIT 1":

or:

var theRow = scoreTable.AsEnumerable().Where(row => row.Field<int>("UnixTS") > fromUnixTS).Max(row => row.Field<int>("Score")).OrderByDescending<int>("UnixTX");

but no luck. OK I am not that experienced with C# but any help out there will be appreciated?

Hmmm, it seems that WHERE can't be used :-( But I have found a way forward:

query = "UnixTS > " + fromUnixTS";
theRow = scoreTable.Select(query, "Score DESC, UnixTS DESC");

Not the brightest way I must admit. Isn't there a clever way to use the MAX?

Mik D
  • 13
  • 3

1 Answers1

1

Something like this?

DataTable scoreTableNew = scoreTable.AsEnumerable().Where(x => x.Field<DateTime>("UnixTS") > DateTime.Now.AddHours(-1)).OrderByDescending(y => y["Score"]).Take(1).CopyToDataTable();

We first get all the rows from the last hour, sort them by the score in a descending order and take the top row.

There is no MAX in Linq, unless you use the morelinq extension (you can get morelinq from NuGet). It has MaxBy.

UPDATE

        DataTable scoreTable = new DataTable();
        scoreTable.Columns.Add("UnixTS", typeof(DateTime));
        scoreTable.Columns.Add("Score", typeof(int));

        scoreTable.Rows.Add(DateTime.Now.AddMinutes(-30), 10);
        scoreTable.Rows.Add(DateTime.Now.AddMinutes(-130), 40);
        scoreTable.Rows.Add(DateTime.Now.AddMinutes(-59), 20);
        scoreTable.Rows.Add(DateTime.Now.AddMinutes(-59), 20);
        scoreTable.Rows.Add(DateTime.Now.AddMinutes(-20), 25);

        DataTable scoreTableNew = scoreTable.AsEnumerable().Where(x => x.Field<DateTime>("UnixTS") > DateTime.Now.AddHours(-1)).OrderByDescending(y => y["Score"]).Take(1).CopyToDataTable();

        Response.Write(Convert.ToDateTime(scoreTableNew.Rows[0][0]).ToShortDateString() + " - " + scoreTableNew.Rows[0][1].ToString());
VDWWD
  • 35,079
  • 22
  • 62
  • 79
  • Hi VDWWD - Tried directly but also altered it a bit to: DataTable scoreTableNew = scoreTable.AsEnumerable().Where(x => x.Field("UnixTS") > DateTime.Now.AddHours(-1)).OrderByDescending(y => y("Score")).Take(1).CopyToDataTable(); It looks as if it is closer, however, neither gives any rows in the new table. – Mik D Aug 24 '16 at 16:29
  • What is the datatype of UnixTS? Maybe it needs converting: http://stackoverflow.com/questions/249760/how-to-convert-a-unix-timestamp-to-datetime-and-vice-versa – VDWWD Aug 24 '16 at 16:58
  • Integer, but I don't think it is the problem as I tried with both int and string formats. The table however stores it in raw format, i.e. 1472153714. I tried with both int and string comparisons. To be more precise: Where(x => x.Field("UnixTS") > fromUnixTS). – Mik D Aug 25 '16 at 19:38
  • Minor detail is that a dual sort is needed to get the latest max within latest hour. Otherwise it can be any of the max values I believe in case there are more than one. – Mik D Aug 25 '16 at 19:46
  • Updated my answer with an example. Try changing the `AddMinutes` to simulate your database values. You will see that it works. @Mik D: No it's not needed because the latest hour is already the only data due to the 'Where` selection. You could use the dual sort to get the data also but in my example it's not needed. – VDWWD Aug 25 '16 at 19:53
  • Actually dual sort only works if the timestamp is accurate to whole hours. – VDWWD Aug 25 '16 at 20:03
  • Well, the UnixTS number sin the table is already in native format but I have not tried to round them to the relevant hour. However, doing so will then take away the opportunity to find the latest max within the latest hour. Yet, it is a time number but in essence it is a plain integer and just to find the max score within 'now integer' - 3600 for the UnixTS. – Mik D Aug 28 '16 at 21:39