min-max string (continue)

See related question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25942283.html

I would like to use Order by in the query, but I am not sure how to do that. Could you please help?
johnkainnAsked:
Who is Participating?
 
ThomasianCommented:
Ok. But you haven't answered my other question. On which column do you need to sort?

If you just need to result sorted using a column, you just need to add ORDER BY at the end of the select statement followed by the column name/number.

SELECT T.TId, T.Num, COALESCE(A.ANumText, CAST(T.TDefault as varchar)) ANumText
FROM @T T OUTER APPLY
    (SELECT CASE WHEN MIN(ANum)<>MAX(ANum)
                 THEN CAST(MIN(ANum) as varchar) + ' - ' + CAST(MAX(ANum) as varchar)
                 WHEN MIN(ANum)<>0
                 THEN CAST(MIN(ANum) as varchar)
            END ANumText
       FROM @A
       WHERE TId = T.TId
    ) A
ORDER BY ANumText
0
 
ThomasianCommented:
Which query did you use? What field do you need to sort the result?

If you just need to sort the result using one of the fields, you can just add ORDER BY at the end of the query.

e.g.

SELECT ..
FROM...
ORDER BY ANumText
0
 
johnkainnAuthor Commented:
Can you please have a look at the related question or click the link above.
I would like to use order by in the query in the last comment.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sevlaganCommented:

SELECT a.TID, CASE WHEN MAX(b.ANum) = MIN(b.ANum) THEN CAST( MIN(b.ANum) as VARCHAR(5) )  
                  ELSE CAST( MIN(b.ANum) AS VARCHAR(5)) + '-' + CAST( MAX(b.ANum) as VARCHAR(5) )      END AS ANumText
FROM @T AS a JOIN @A AS b ON a.TId = b.TId
GROUP BY a.TId
0
 
ronan1979Commented:
even if you don't need anymore, someone else might - about the subject - Trees in SQL SERVER : http://msdn.microsoft.com/en-us/library/aa175807%28SQL.80%29.aspx

as said @Thomasian

ASC ascendant order
DESC descendant order

ORDER BY T.TId ASC;
ORDER BY T.TId DESC;
ORDER BY T.Num DESC;
ORDER BY T.Num DESC;
ORDER BY T.ANumText DESC;
ORDER BY T.ANumText DESC;
0
 
johnkainnAuthor Commented:
I have an additional column in @T table that is called MyText.
I need to sort by it. If I add Order by MyText at the end of the query I get returned multiple rows for each row in @T.

0
 
ThomasianCommented:
Can you post the actual query? Adding an order by clause on the query should not cause it to return more/less records.
0
 
sevlaganCommented:
This should work:

SELECT a.TID, CASE WHEN MAX(b.ANum) = MIN(b.ANum) THEN CAST( MIN(b.ANum) as VARCHAR(5) )  
                  ELSE CAST( MIN(b.ANum) AS VARCHAR(5)) + '-' + CAST( MAX(b.ANum) as VARCHAR(5) )      END AS ANumText
FROM @T AS a JOIN @A AS b ON a.TId = b.TId
GROUP BY a.TId
0
 
johnkainnAuthor Commented:
Thanks. I found out the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.