Sort according to the last column (or any other column with a numerical value) of a csv file

Question

I have a file with following contents. I am looking to sort this file based on the last column (and third last but for another file) while retaining rest of the contents of the row.

ABC,DEF,GHI,-5,-8,-0.6,0.488 
XYZ,JKL,MNO,3,-5,0.2,-0.342 
STU,WXY,DEF,-1,4,0.01,0.345 

If I use this command, it works as expected and shows the correct results:

awk '{print $NF,$0}' FILE | sort -nr | cut -f2- -d' '

XYZ,JKL,MNO,3,-5,0.2,-0.342 
STU,WXY,DEF,-1,4,0.01,0.345
ABC,DEF,GHI,-5,-8,-0.6,0.488 

But the same command on a bigger file gives an incorrect result. (The file I am looking to sort has 4M rows) Input:

ABC,DEF,GHI,-5,-8,-0.6,0.0488 
XYZ,JKL,MNO,3,-5,0.2,-0.0342 
STU,WXY,DEF,-1,4,0.01,0.0345 
JKL,JKL,GHI,-2,-3,0.31,-0.0524 
QRS,GHI,YUT,-3,-1,0.20,-0.0503 
HUR,JTL,ZST,1,1,0.52,-0.0556 
FTT,JL,MKI,0,2,0.21,-0.0529 
FTC,JKL,ERW,-1,6,0.23,-0.0441 
HJI,MHP,VGT,1,-6,0.80,-0.0433 
BUT,IOP,HGT,2,2,0.2,-0.0439 
XYZ,BGY,MNO,-2,1,0.01,-0.0416 

Answer 1

If you know how many fields you have:

$ sort -t, -k7,7n file
XYZ,JKL,MNO,3,-5,0.2,-0.342
STU,WXY,DEF,-1,4,0.01,0.345
ABC,DEF,GHI,-5,-8,-0.6,0.488

or if you don't:

$ awk 'BEGIN{FS=OFS=","} {print $NF,$0}' file | sort -t, -k1,1n | cut -d, -f2-
XYZ,JKL,MNO,3,-5,0.2,-0.342
STU,WXY,DEF,-1,4,0.01,0.345
ABC,DEF,GHI,-5,-8,-0.6,0.488

and to sort by the 3rd-last instead of last field would obviously just be:

$ awk 'BEGIN{FS=OFS=","} {print $(NF-2),$0}' file | sort -t, -k1,1n | cut -d, -f2-
ABC,DEF,GHI,-5,-8,-0.6,0.488
XYZ,JKL,MNO,3,-5,0.2,-0.342
STU,WXY,DEF,-1,4,0.01,0.345

If you want to retain input order when multiple rows have the same value for the field you're sorting on then if you have GNU sort you can use -s, otherwise include the line number as a secondary sort key:

$ awk 'BEGIN{FS=OFS=","} {print $NF,NR,$0}' file | sort -t, -k1,1n -k2,2n | cut -d, -f3-
XYZ,JKL,MNO,3,-5,0.2,-0.342
STU,WXY,DEF,-1,4,0.01,0.345
ABC,DEF,GHI,-5,-8,-0.6,0.488

Answer 2

Assuming the input is relatively simple (delimiter does not appear in the records) you can sort by a given column like this:

$ c=$(< input awk -F, 'NR==1 { print NF; exit }')
$ < input sort -t, -k $c,${c}n --debug

output:

XYZ,JKL,MNO,3,-5,0.2,-0.342
                     ______
___________________________
STU,WXY,DEF,-1,4,0.01,0.345
                      _____
___________________________
ABC,DEF,GHI,-5,-8,-0.6,0.488
                       _____
____________________________

Here, we say that sort is using a comma as the delimiter between fields and sort (only) by a specific field, which we figured out to be last in the previous step.

By the way, your question is likely to get closed because no doubt it has been asked and answered before.