with what logic results are retrieved when a aggreerate function is applied on columns having text data ..?

Question

So I was retrieving some values from few set of tables of a database for making a dataset.Each and every column of that dataset is dependent on the value of a column having primary key. So the problem is here... there is column having textual data in the dataset which will be affecting to the value of a columns next to it. So lets consider them as col1 and col2 . now I was trying to get the values of col1 by using aggregate functions like MAX,MIN and they were giving me correct result for a set of primary keys. after some time when primary keys changes or I apply this logic with another dataset with same database its not giving me correct values.

I think its perfectly working for the columns having 2 values like 'A' or 'B' and the moment number of values increases by 3 and more its not working out correctly. is there solution for this?

Answer 1

MAX and MIN, when applied to text essentially sort the whole column alphabetically (according to the collation of he column or the db) and then take the last (max) or first (min) value respectively

I've never encountered a problem with this as an algorithm; it's always correctly chosen the alphabetically latest or earliest value regardless of the count of values but you should bear in mind that collation will affect things; different languages sort differently and you should closely consider how a culture sorts it's alphabet when looking to my n or max text

Also remember min and max and grouping operations mix row data up so you don't get to keep other data from the same row when it is part of a grouping operation. If you want eg "the latest row as defined by textcolumn1, plus all the other data from that row" you'd probably need to use row_number() over(order by textcolumn1) and then pick the row where the output of row number was 1