Min and Max or Range

[capri]

Hi,

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.

Thanks

 

[jmcilhinney]

You want to do this in SQL code or C# code?

 

[capri]

In C# code

 

[jmcilhinney]

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))
                .Distinct()
                .ToArray();
}

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);
}