Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Please help me to find out maximum value of a field based on grouping of other fields.

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Please help me to find out maximum value of a field based on grouping of other fields.
# 1  
Old 01-17-2012
Please help me to find out maximum value of a field based on grouping of other fields.
Please help me to find out maximum value of a field based on grouping of other fields, as we do in SQL.

Like in SQL if we are having below records :
Code:
Client_Name Associate_Name  Date1           Value
C1111          A1111                2012-01-17   10
C1111          A1111                2012-01-17   20
C1111          A1111                2012-01-17   30
C1111          A1111                2012-01-17   40
C1111          A1111                2012-01-17   50
C2222          A2222                2012-01-17   60
C2222          A2222                2012-01-17   70
C2222          A2222                2012-01-17   80
C2222          A2222                2012-01-17   80
C2222          A2222                2012-01-17   100

and we find max of value group by Client_Name, Associate_Name and Date1 then we use :
Code:
Select
Client_Name,
Associate_Name,
Date1,
max(Value)
from table_t
group by
Client_Name,
Associate_Name,
Date1

and we get output like below :
Code:
Client_Name Associate_Name  Date1           Max(Value)
C1111          A1111                2012-01-17   50
C2222          A2222                2012-01-17   100

I want same output from a file using unix command or program :
Code:
Client_Name,Associate_Name,Date1,Value
C1111,A1111,2012-01-17,10
C1111,A1111,2012-01-17,20
C1111,A1111,2012-01-17,30
C1111,A1111,2012-01-17,40
C1111,A1111,2012-01-17,50
C2222,A2222,2012-01-17,60
C2222,A2222,2012-01-17,70
C2222,A2222,2012-01-17,80
C2222,A2222,2012-01-17,90
C2222,A2222,2012-01-17,100

I want below output :
Code:
Client_Name,Associate_Name,Date1,Value
C1111,A1111,2012-01-17,50
C2222,A2222,2012-01-17,100

Please provide me the resolution.
Last edited by Scott; 01-17-2012 at 03:01 PM.. Reason: Please use code tags
# 2  
Old 01-20-2012
This awk script will (almost) do what you need:
Code:
BEGIN { FS = OFS = ","; }
NR == 1 { print; next; }
{ f = $1 FS $2 FS $3; if (m[f] < $4) { m[f] = $4; } }
END { for (f in m) { print f, m[f]; } }

However, the output is not ordered:
Code:
awk -f awkscriptfile datafile
Client_Name,Associate_Name,Date1,Value
C2222,A2222,2012-01-17,100
C1111,A1111,2012-01-17,50

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print lines based on text in field and value in two additional fields

In the awk below I am trying to print the entire line, along with the header row, if $2 is SNV or MNV or INDEL. If that condition is met or is true, and $3 is less than or equal to 0.05, then in $7 the sub pattern :GMAF= is found and the value after the = sign is checked. If that value is less than... (0 Replies)
Discussion started by: cmccabe
0 Replies

2. Shell Programming and Scripting

Find minimum and maximum values based on column with associative array

Hello, I need to find out the minimum and maximum values based on specific column, and then print out the entire row with the max value. Infile.txt: scf6 290173 290416 . + X_047241 T_00113118-1 scf6 290491 290957 . + X_047241 T_00113118-2 scf6 290898 290957 . + X_047241 T_00113119-3 scf6... (2 Replies)
Discussion started by: yifangt
2 Replies

3. Shell Programming and Scripting

Find fields based on date criterion

Hi All , I am trying to find some non empty files from a directory based on below conditions : Files:: SIZE DATE FILE 3679 Jan 25 23:59 belk_rpo_error_po9324892_01252014.log 0 Jul 01 06:30 belk_rpo_error_po9324267_07012014.log 0 Jul 20 05:50... (7 Replies)
Discussion started by: LoneRanger
7 Replies

4. Shell Programming and Scripting

How to print 1st field and last 2 fields together and the rest of the fields after it using awk?

Hi experts, I need to print the first field first then last two fields should come next and then i need to print rest of the fields. Input : a1,abc,jsd,fhf,fkk,b1,b2 a2,acb,dfg,ghj,b3,c4 a3,djf,wdjg,fkg,dff,ggk,d4,d5 Expected output: a1,b1,b2,abc,jsd,fhf,fkk... (6 Replies)
Discussion started by: 100bees
6 Replies

5. Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Dear List, I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

6. Shell Programming and Scripting

Grouping files according to certain fields in their name

I have a list of fils stored insortedLst, and want to select certain fields to group specific files together: Example of the files would be as below: n02-z30-dsr65-ndelt0.25-varp0.002-16x12drw-run1.log n02-z30-dsr65-ndelt0.25-varp0.002-16x12drw-run2.log... (2 Replies)
Discussion started by: kristinu
2 Replies

7. Shell Programming and Scripting

Merging CSV fields based on a common field

Hi List, I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So: File 1:... (1 Reply)
Discussion started by: landossa
1 Replies

8. Shell Programming and Scripting

grouping based on first column

I do have a tab delimited file of the following format a_1 rt a_1 st_2 a_1 st_3 a_2 bt_2 a_2 st_er b_2 st_2 b_2 st_32 S_1 rt_8 S_1 rt_64 I want to cut short the above file and group the file based on the first column like below. a_1 rt st_2 st_3 a_2 bt_2 st_er b_2 st_2... (1 Reply)
Discussion started by: Lucky Ali
1 Replies

9. Shell Programming and Scripting

Sorting on two fields time field and number field

Hi, I have a file that has data in it that says 00:01:48.233 1212 00:01:56.233 345 00:09:01.221 5678 00:12:23.321 93444 The file has more line than this but i just wanted to put in a snippet to ask how I would get the highest number with time stamp into another file. So from the above... (2 Replies)
Discussion started by: pat4519
2 Replies

10. Shell Programming and Scripting

Find top N values for field X based on field Y's value

I want to find the top N entries for a certain field based on the values of another field. For example if N=3, we want the 3 best values for each entry: Entry1 ||| 100 Entry1 ||| 95 Entry1 ||| 30 Entry1 ||| 80 Entry1 ||| 50 Entry2 ||| 40 Entry2 ||| 20 Entry2 ||| 10 Entry2 ||| 50... (1 Reply)
Discussion started by: FrancoisCN
1 Replies
Login or Register to Ask a Question