Gloria Santin Gloria Santin - 2 months ago 6
SQL Question

GROUP BY Result does not return the Max() string

I am using recursion within a common table expression (CTE) to concatenate multiple rows. The recursion works fine. But in the

SELECT
statement after the CTE I want to return only the rows with the concatenated string. But I use MAX(stringValue) it returns the shortest string. When I use MIN(stringValue) it returns the correct (longest) string. Why is that?

This is the data in the CTE when returning all of the rows:

Row TableName Script
1 Activity This is a string that I created.
1 Table2 This is another string.
2 Table2 This is another string. This is another string.
1 Table3 Test string.
2 Table3 Test string. Test string 2.
3 Table3 Test string. Test string 2. Test string 3.


When I use this query to return only the rows with the MAX(Row):

SELECT MAX(Row) AS RowNumber, MAX(TableName) AS Tbl, MAX(Script)
FROM CTE
GROUP BY TableName


My Result is this:

Row TableName Script
1 Activity This is a string that I created.
2 Table2 This is another string.
3 Table3 Test string.


The correct Row is returned but NOT the correct script.
When I using this query:

SELECT MAX(Row) AS RowNumber, MAX(Table) AS Tbl, MIN(Script)
FROM CTE
GROUP BY TableName


I get the correct data, ie. the longest string.
Why is this?

I used this in another query and the correct string was returned, ie the longest string.

I am concerned that these results will be unpredictable and inconsistent.

UPDATE
I want to return these rows:

Row TableName Script
1 Activity This is a string that I created.
2 Table2 This is another string. This is another string.
3 Table3 Test string. Test string 2. Test string 3.

Answer

Your max() should be returning the row that you want. But, you can also get the latest version using:

select t.*
from (select cte.*, row_number() over (partition by table order by row desc) as seqnum
      from cte
     ) t
where seqnum = 1;

This returns the row with the largest row. You could also get the longest string by using order by len(script) desc.

I note that your code that doesn't work uses both table and tablename. I'm not sure if that explains the unexpected results.

Comments