Min and Max or Range


Active member
Jun 16, 2015
Programming Experience

I have a table in SQL like so

UId Class
1 Apple
2 Apple
3 Apple
3a Apple
4 Orange
4a Orange
4b Orange
5 Orange

I am trying to get the min and max of the UId column for each class as below.

1 - 3 Apple
4 - 5 Orange

Is it possible to do so, as the UId is varchar.

One option for extracting numbers from the head of strings is the following:
private int GetLeadingInteger(string text)
    var leadingDigits = text.TakeWhile(char.IsDigit).ToArray();
    var number = leadingDigits.Length == 0
                     ? 0 // Return zero if there are no leading digits.
                     : Convert.ToInt32(new string(leadingDigits));

    return number;

Here's a method that you can use to get the distinct values from a DataColumn, e.g. the Class column in your data:
private T[] GetDistinctValues<T>(DataTable table, string columnName)
    return table.AsEnumerable()
                .Select(row => row.Field<T>(columnName))

Using those two methods:
var table = new DataTable();

// Populate table using appropriate query.

foreach (var @class in GetDistinctValues<string>(table, "Class"))
    var rows = table.Select($"Class = '{@class}'");
    var uids = rows.Select(row => row.Field<string>("Uid")).ToArray();
    var min = uids.Min(GetLeadingInteger);
    var max = uids.Max(GetLeadingInteger);

    Console.WriteLine("{0} - {1} {2}", min, max, @class);
Top Bottom