Help: Find line where column has max vlaue


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help: Find line where column has max vlaue
# 1  
Old 11-18-2009
Help: Find line where column has max vlaue

Hi all,

Ive searched the forum but with no luck...

I have a file:

ID Name Val
1 bob 45
2 joe 89
3 sue 11
4 steve 89
...
etc

I want to find a way to print lines which have the largest value in the 'Val' column...i say lines as you may have one or more with the same value.

Output should be (if one or more same highest - otherwise only one row):
2 joe 89
4 steve 89
# 2  
Old 11-18-2009
You can do somthing like that (assume no header in input file) :
Code:
sort -k3nr input_file| awk '
NR==1 { max = $3 }
$3<max { exit }
1
'

Jean-Pierre.
# 3  
Old 11-18-2009
Code:
 awk 'NR==1{next} $3==max{s=s"\n"$0} $3>max{max=$3;s=$0} END{print s}' infile

# 4  
Old 11-18-2009
Quote:
Originally Posted by aigles
You can do somthing like that (assume no header in input file) :
Code:
sort -k3nr input_file| awk '
NR==1 { max = $3 }
$3<max { exit }
1
'

Jean-Pierre.
I dont get anything?

What if the input file has comma separated values?
# 5  
Old 11-18-2009
Works fine on my AIX box :
Code:
$ cat muay.sh
sort -k3nr muay.txt| awk '
NR==1 { max = $3 }
$3<max { exit }
1
'
$ cat muay.txt
1 bob 45
2 joe 89
3 sue 11
4 steve 33
5 averell 89
6 willian 89
7 lucky 9
8 jack 89
$ ./muay.sh
2 joe 89
5 averell 89
6 willian 89
8 jack 89
$

If the delimiter is comma :
Code:
sort -t, -k3nr muay.txt| awk -F, '
NR==1 { max = $3 }
$3<max { exit }
1
'

Jean-Pierre.
# 6  
Old 11-18-2009
Comma-delimited version:
Code:
awk -F, 'NR==1{next} $3==max{s=s"\n"$0} $3>max{max=$3;s=$0} END{print s}' infile


Last edited by Scrutinizer; 11-18-2009 at 10:39 AM..
# 7  
Old 11-18-2009
Code:
sort +2 -nr a.txt | awk 'NR==1{print;max=$3;}NR>1{if($3==max){print;}}'

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find Max value of line and print

I need to find the max value of all columns except the 1st column and print the answer along with the 1st column. Input 123xyz 0 0 1 2 0 0 0 0 0 0 0 234xyz 0 0 0 0 0 0 0 0 0 0 0 345xyz 0 0 1 0 0 0 ... (8 Replies)
Discussion started by: ncwxpanther
8 Replies

2. Shell Programming and Scripting

Get min and max value in column

Gents, I have a big file file like this. 5100010002 5100010004 5100010006 5100010008 5100010010 5100010012 5102010002 5102010004 5102010006 5102010008 5102010010 5102010012 The file is sorted and I would like to find the min and max value, taking in the consideration key1... (3 Replies)
Discussion started by: jiam912
3 Replies

3. UNIX for Dummies Questions & Answers

Using awk to find max and printing entire line

Hi folks, I am very new to awk. I have what is probably a very simple question. I'm trying to get the max value of column 1, but also print column 2. My data looks like this: 0.044|2000-02-03 14:00:00 5.23|2000-02-03 05:45:00 5.26|2000-02-03 11:15:00 0|2000-02-01 18:30:00 So in this case... (2 Replies)
Discussion started by: amandarobe
2 Replies

4. Shell Programming and Scripting

Get the MAX value out of a column

I've the following data set. I would like to look at the column 3 and only use the rows which has the max value for column 3 Can we use the awk or sed to achieve it. 10 2 10 100 11 2 20 100 12 2 30 100 13 2 30 100 14 ... (7 Replies)
Discussion started by: rudoraj
7 Replies

5. UNIX for Dummies Questions & Answers

get max value every 4 rows between 2 column

Hi all I have a file that has two columns and I need the maximum value in column 2 of 4 positions o rows. for example at position {1..3} there are 4 characters (A, C, G and T) each of these characters with a value with a value in column 2. I need the maximum value in column 2 and the corresponding... (2 Replies)
Discussion started by: xinox
2 Replies

6. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Hi, I need an awk script (or whatever shell-construct) that would take data like below and get the max value of 3 column, when grouping by the 1st column. clientname,day-of-month,max-users ----------------------------------- client1,20120610,5 client2,20120610,2 client3,20120610,7... (3 Replies)
Discussion started by: ckmehta
3 Replies

7. Shell Programming and Scripting

to find min and max value for each column!

Hello Experts, I have got a txt files which has multiple columns, I want to get the max, min and diff (max-min) for each column in the same txt file. Example: cat file.txt a 1 4 b 2 5 c 3 6 I want ouput like: cat file.txt a 1 4 b 2 5 c 3 6 Max 3 6 Min 1 4 Diff 2 2 awk 'min=="" ||... (4 Replies)
Discussion started by: dixits
4 Replies

8. Shell Programming and Scripting

Find in first column and replace the line with Awk, and output new file

Find in first column and replace the line with Awk, and output new file File1.txt"2011-11-02","Georgia","Atlanta","x","","" "2011-11-03","California","Los Angeles","x","","" "2011-11-04","Georgia","Atlanta","x","x","x" "2011-11-05","Georgia","Atlanta","x","x","" ... (4 Replies)
Discussion started by: charles33
4 Replies

9. UNIX for Dummies Questions & Answers

loop? print max column in each line for 800 files and merge

Hello, I have 800 or so files with 3 columns each and >10000 lines each. For each file and each line I would like to print the maximum column number for each line. Then I would like to 'paste' each of these files together (column-wise) so that the file with expression in label '_1' is the... (6 Replies)
Discussion started by: peanuts48
6 Replies

10. Shell Programming and Scripting

loop in awk - column max for each column

Hello all, this should really be easy for you... I need AWK to print column maxima for each column of such input: Input: 1 2 3 1 2 1 1 3 2 1 1 2 Output should be: 2 2 3 3 This does the sum, but i need max instead: { for(i=1; i<=NF; i++) sum +=$i } END {for(i=1; i in sum;... (3 Replies)
Discussion started by: irrevocabile
3 Replies
Login or Register to Ask a Question