Sort on column

06-25-2013

Registered User

50, 0

Join Date: May 2013

Last Activity: 9 July 2018, 4:32 PM EDT

Location: Toronto,Canada

Posts: 50

Thanks Given: 15

Thanked 0 Times in 0 Posts

Sort on column

How to sort based on the 4 the column . The input data has a header and output needs to be sorted based on the 4th column rbcid.

I tried below code but not getting results

Code:

sort -u -t'|' -k4,4r file1 > file2

Code:

time|tourit|nofdays|rbcid|blank|type|value|nill|valuedesc|name
2013-05-16T00:52:31.662-04:00|12|3|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO ABLatest
2013-05-15T00:52:31.672-04:00|1|40|39693766-NA-NA||Common Stick|ESHR||Common Stock|HS AG
2013-05-14T00:52:31.672-04:00|1|45|16111278-TSX-NA||Common Stk|ESQHR||Common Stock|STANDARD REGISTER CO
2013-05-14T00:52:31.662-04:00|1|4|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO AB
2013-05-13T00:52:31.672-04:00|1|44|15277105-NA-NA||Common Stock|DOMESHR||Common Stock|CYRO AB
2013-05-10T00:52:31.672-04:00|1|5|39693766-NYSE-EUR||Common HSAGStick|ESHR||Common Stock|HS AG

samrat dutta

View Public Profile for samrat dutta

Find all posts by samrat dutta

06-25-2013

Registered User

962, 67

Join Date: Mar 2005

Last Activity: 16 January 2019, 9:30 PM EST

Location: Philadelphia metro

Posts: 962

Thanks Given: 3

Thanked 67 Times in 61 Posts

please post sample input file as well as expected output ...

Just Ice

View Public Profile for Just Ice

Find all posts by Just Ice

06-25-2013

Registered User

50, 0

Join Date: May 2013

Last Activity: 9 July 2018, 4:32 PM EDT

Location: Toronto,Canada

Posts: 50

Thanks Given: 15

Thanked 0 Times in 0 Posts

Sample Input:

Code:

time|tourit|nofdays|rbcid|blank|type|value|nill|valuedesc|name
2013-05-16T00:52:31.662-04:00|12|3|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO ABLatest
2013-05-15T00:52:31.672-04:00|1|40|39693766-NA-NA||Common Stick|ESHR||Common Stock|HS AG
2013-05-14T00:52:31.672-04:00|1|45|16111278-TSX-NA||Common Stk|ESQHR||Common Stock|STANDARD REGISTER CO
2013-05-14T00:52:31.662-04:00|1|4|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO AB
2013-05-13T00:52:31.672-04:00|1|44|15277105-NA-NA||Common Stock|DOMESHR||Common Stock|CYRO AB
2013-05-10T00:52:31.672-04:00|1|5|39693766-NYSE-EUR||Common HSAGStick|ESHR||Common Stock|HS AG

Sample Output:

Code:

time|tourit|nofdays|rbcid|blank|type|value|nill|valuedesc|name
2013-05-16T00:52:31.662-04:00|12|3|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO ABLatest
2013-05-14T00:52:31.662-04:00|1|4|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO AB
2013-05-13T00:52:31.672-04:00|1|44|15277105-NA-NA||Common Stock|DOMESHR||Common Stock|CYRO AB
2013-05-15T00:52:31.672-04:00|1|40|39693766-NA-NA||Common Stick|ESHR||Common Stock|HS AG
2013-05-10T00:52:31.672-04:00|1|5|39693766-NYSE-EUR||Common HSAGStick|ESHR||Common Stock|HS AG
2013-05-14T00:52:31.672-04:00|1|45|16111278-TSX-NA||Common Stk|ESQHR||Common Stock|STANDARD REGISTER CO

samrat dutta

View Public Profile for samrat dutta

Find all posts by samrat dutta

06-25-2013

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

Code:

head -1 file; tail -n+2 file | sort -t'|' -k4,4r
time|tourit|nofdays|rbcid|blank|type|value|nill|valuedesc|name
2013-05-10T00:52:31.672-04:00|1|5|39693766-NYSE-EUR||Common HSAGStick|ESHR||Common Stock|HS AG
2013-05-15T00:52:31.672-04:00|1|40|39693766-NA-NA||Common Stick|ESHR||Common Stock|HS AG
2013-05-14T00:52:31.672-04:00|1|45|16111278-TSX-NA||Common Stk|ESQHR||Common Stock|STANDARD REGISTER CO
2013-05-14T00:52:31.662-04:00|1|4|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO AB
2013-05-16T00:52:31.662-04:00|12|3|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO ABLatest
2013-05-13T00:52:31.672-04:00|1|44|15277105-NA-NA||Common Stock|DOMESHR||Common Stock|CYRO AB

BTW - your desired output is NOT what your sort would yield, nor any sort.

RudiC

View Public Profile for RudiC

Find all posts by RudiC

06-25-2013

Registered User

962, 67

Join Date: Mar 2005

Last Activity: 16 January 2019, 9:30 PM EST

Location: Philadelphia metro

Posts: 962

Thanks Given: 3

Thanked 67 Times in 61 Posts

copied your data into 900.txt ... ran sort -t "|" -d -k 4 900.txt after removing the first line and got this ...

Code:

2013-05-13T00:52:31.672-04:00|1|44|15277105-NA-NA||Common Stock|DOMESHR||Common Stock|CYRO AB
2013-05-14T00:52:31.662-04:00|1|4|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO AB
2013-05-16T00:52:31.662-04:00|12|3|15277105-NA-YEN||Common Stick|ESHR||Common Stock|CYRO ABLatest
2013-05-14T00:52:31.672-04:00|1|45|16111278-TSX-NA||Common Stk|ESQHR||Common Stock|STANDARD REGISTER CO
2013-05-15T00:52:31.672-04:00|1|40|39693766-NA-NA||Common Stick|ESHR||Common Stock|HS AG
2013-05-10T00:52:31.672-04:00|1|5|39693766-NYSE-EUR||Common HSAGStick|ESHR||Common Stock|HS AG

Just Ice

View Public Profile for Just Ice

Find all posts by Just Ice

06-26-2013

Registered User

50, 0

Join Date: May 2013

Last Activity: 9 July 2018, 4:32 PM EDT

Location: Toronto,Canada

Posts: 50

Thanks Given: 15

Thanked 0 Times in 0 Posts

Thanks.
I just wanted that the 4th column rbcid value should be sorted in such a way that all like ones are in sequence no matter what the values are.
Like rbcid value starting with 15277105 should be in a order (3 in all) one after another irrespective of the values it has. All like ones in a order.

samrat dutta

View Public Profile for samrat dutta

Find all posts by samrat dutta

06-26-2013

Registered User

454, 69

Join Date: Sep 2006

Last Activity: 9 March 2020, 5:17 AM EDT

Location: Bangalore, India

Posts: 454

Thanks Given: 29

Thanked 69 Times in 68 Posts

Shell script solution

I tried this and it worked for me. This could be a lengthy process but works.

Code:

tail +2 900.txt > 901.txt
awk -F"|" '{printf("%s,%d\n",NR,$4) }' 901.txt >902.txt
sort -t"," -nrk2,2 902.txt >903.txt
head -1 900.txt
while read LINE
do
 linenr=$(echo $LINE | cut -d"," -f 1)
 awk -v lnr=$linenr '(NR==lnr) {print}' 901.txt
done <903.txt

To explain the logic, the 4th field with only numeric portion is extracted into a separate file 902.txt and it is printed along with a line number. Then the data is sorted on the second field in 902.txt. The first field gives the re-ordered line numbers. Then this re-ordered line number is used to compare with original file and print corresponding row. For each line in 902.txt, the original file is read completely and only the matching line number is printed.

Last edited by krishmaths; 06-26-2013 at 04:45 AM.. Reason: Added explanation for code

krishmaths

View Public Profile for krishmaths

Find all posts by krishmaths

Shell Programming and Scripting

Sort on column

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sort based on one column

Discussion started by: kshitij

2. Shell Programming and Scripting

Use sort to sort numerical column

Discussion started by: sand1234

3. UNIX for Dummies Questions & Answers

Sort each column independently

Discussion started by: a_bahreini

4. UNIX for Dummies Questions & Answers

Sort command in one column and not effect to another column

Discussion started by: GeodusT

5. Shell Programming and Scripting

Edit column using sort

Discussion started by: ipatah

6. Shell Programming and Scripting

Sort data As per first Column

Discussion started by: pareshkp

7. UNIX for Dummies Questions & Answers

Sort on one column only

Discussion started by: gio001

8. Shell Programming and Scripting

sort on second column only based on first column

Discussion started by: malcomex999

9. Shell Programming and Scripting

Question about sort specific column and print other column at the same time !

Discussion started by: patrick87

10. UNIX for Dummies Questions & Answers

Sort by the third column with +n command

Discussion started by: bobo