0

What is the logic behind MIN() aggregate function to evaluate data-types like 'CHAR' or 'VARCHAR2' ?

Rubbal Bhusri
  • 69
  • 1
  • 4
  • 15

2 Answers2

1

Min( VarcharType ) will return the lowest string value for that column in the result set, given the sorting order of the column in question.

Curt
  • 5,518
  • 1
  • 21
  • 35
  • How this string value is decided ? Let's take a practical example, if we were to find MIN() out of 'Curt' and 'Burt' ? How string value is determined of these two words ? or the string values are set according to the alphabetical order e.g. a – Rubbal Bhusri Aug 04 '13 at 18:23
  • 1
    @RubbalBhusri "Burt". If you were to sort all the values of that column using the collation order active for that column, MIN() would return the first value, and MAX() would return the last value. – Curt Aug 04 '13 at 18:26
  • I think u said just the opposite, I just now tried on my Oracle DBMS, MIN() returns the last value of the column and MAX() returns the first value of the column. But anyways, your answer helped me in noticing the difference. – Rubbal Bhusri Aug 04 '13 at 18:30
  • 1
    @RubbalBhusri If `MAX()` returned the (alphabetically) first result, then that column was sorted in descending order: take a look at the underlying indices to confirm that that is the case. What I said is true for columns sorted in "natural" (i.e. ascending) order. – Curt Aug 05 '13 at 15:59
  • 2
    `SELECT name FROM Animal ORDER BY name` will return `'Aardvark', 'Bear', 'Gorilla', 'Zebra'`. On the other hand, `SELECT name FROM Animal ORDER BY name DESC` will return `'Zebra', 'Gorilla', 'Bear', 'Aardvark'` `MIN(name)` will return `'Aardvark'`. `MAX(name)` will return `'Zebra'`. – Curt Aug 05 '13 at 17:28
  • and if the values in column are present in the order as : 'Zebra','Aardvark','gorilla','bear' , then What logic will MiN() and MAX() applies ? – Rubbal Bhusri Aug 05 '13 at 18:25
  • and if the values in column are present in the order as : 'Zebra','Aardvark','gorilla','bear' , then What logic will MiN() and MAX() applies ? I mean, Does the existing order of values present in column matters or not ? or MIN() always give words starting with 'A' and MAX() always give words starting with 'Z', if they have to choose from two words starting with 'A' and 'Z' respectively. – Rubbal Bhusri Aug 05 '13 at 18:37
  • 1
    let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/34869/discussion-between-curt-and-rubbal-bhusri) – Curt Aug 05 '13 at 19:19
0

Also MIN() returns the shortest string (in terms of length) but it's alphabetically ordered.

eg- There are two fruits : 1. Apple, 2. Kiwi. Output: Apple (because A comes before K although Kiwi is shorter than Apple).

Stan_Dodo
  • 1
  • 3