3

I am trying to sort a file:

TX001;A0004|Number|5|0|Y|1        
TX001;A0110|VARCHAR|5|0|Y|4         
TX001;A1332|VARCHAR|15|0|Y|3         
TX001;B3321|VARCHAR|15|0|Y|5         
TX001;C1321|VARCHAR|15|0|Y|2      
TY001;A1234|Number|5|0|Y|3        
TY001;C2335|VARCHAR|5|0|Y|1        
TY001;B2133|VARCHAR|15|0|Y|2        
TZ001;A1234|Number|5|0|Y|3    
TZ001;A2133|VARCHAR|5|0|Y|1  
TZ001;C0133|VARCHAR|15|0|Y|2

The expected output result is:

TX001;A0004|Number|5|0|Y|1   
TX001;C1321|VARCHAR|15|0|Y|2   
TX001;A1332|VARCHAR|15|0|Y|3  
TX001;A0110|VARCHAR|5|0|Y|4  
TX001;B3321|VARCHAR|15|0|Y|5  
TY001;C2335|VARCHAR|5|0|Y|1  
TY001;B2133|VARCHAR|15|0|Y|2  
TY001;A1234|Number|5|0|Y|3  
TZ001;A2133|VARCHAR|5|0|Y|1      
TZ001;C0133|VARCHAR|15|0|Y|2  
TZ001;A1234|Number|5|0|Y|3

I need to sort based on the last numeric column. When I try the sort using -n, the order of the first column is changing.

Tried using sort -t "|" -k 6,6n FNAM.txt but it results numerical sort chaning the first column order.

I need to sort based on the last column numerically, keeping the first column of table name unaffected.

1
  • 1
    A more accurate description might be "sort on the first five characters of the line, and then on the last numeric field". While the number is always a single digit as in the sample data, there's no difference between a textual sort and a numeric sort, of course. If you added some entries starting 'TA999' to the end of the file and then wanted those entries to remain at the end of the file but still to sort by the sixth field within the group of rows sharing that prefix, then you are in for a much harder job. In your sample data, the prefixes appear in sorted order which simplifies things.
    – Jonathan Leffler
    Dec 15, 2015 at 13:25

2 Answers 2

Reset to default
8

With any POSIX-compliant sort:

sort -t"|" -k1,1.6 -k6n file
  • -t"|" sets the delimiter to |.
  • -k1,1.6 sorts the first field starting from the beginning up to the 6th character 1.
  • -k6n sort then the 6th field numberic.

1 From the manpage:

POS is F[.C][OPTS], where F is the field number and C the character position in the field;
1
  • This works with any POSIX-compliant sort; it does not use any feature specific to GNU sort.
    – Jonathan Leffler
    Dec 15, 2015 at 13:32
6

sort can sort on multiple columns with different types, but it requires the delimiter to be a single character. To handle your example and produce the expected output, you can replace the first ';' with '|', then sort on the first column alphanumerically then on the seventh (formerly sixth) numerically, and finally replace the first '|' with ';':

sed 's/;/|/' < FNAM.txt | sort -t\| -k1,1 -k7,7n | sed 's/|/;/'

The variant you tried,

sort -t "|" -k 6,6n FNAM.txt

sorts the full file only on the sixth column, numerically; sort isn't aware of any structure you don't tell it about, so it doesn't know it should group lines by the first few characters. So you end up with the last column completely sorted, which means mixing up the table names.

3
  • its more simple.
    – mikeserv
    Dec 15, 2015 at 9:28
  • Thanks. this works perfect !! I never tried with changing the delimiter.
    – APS
    Dec 15, 2015 at 9:32
  • The sed operations are wholly unnecessary with this data, though the technique of editing field delimiters can have its uses in other data sets. More generally, you can do things like make a stable sort by adding a line number in a pre-processing step, sorting the data according to the main criteria and then finally by the line number in ascending order, and then remove the line number in a post-processing step.
    – Jonathan Leffler
    Dec 15, 2015 at 13:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .