current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

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

share|improve this question
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 21 hours ago

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
its more simple. – mikeserv yesterday
    
Thanks. this works perfect !! I never tried with changing the delimiter. – APS yesterday
    
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 21 hours ago
up vote 8 down vote

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;
share|improve this answer
    
This works with any POSIX-compliant sort; it does not use any feature specific to GNU sort. – Jonathan Leffler 21 hours ago
    
@JonathanLeffler Thanks, I edit the answer. – chaos 21 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.