Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Please help me to find out maximum value of a field based on grouping of other fields.
Top Forums UNIX for Dummies Questions & Answers Please help me to find out maximum value of a field based on grouping of other fields. Post 302590780 by KamalKumarKalra on Tuesday 17th of January 2012 01:55:26 PM
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
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT DEBIAN

sql::reservedwords

SQL::ReservedWords(3pm) 				User Contributed Perl Documentation				   SQL::ReservedWords(3pm)

NAME

       SQL::ReservedWords - Reserved SQL words by ANSI/ISO

SYNOPSIS

	  if ( SQL::ReservedWords->is_reserved( $word ) ) {
	      print "$word is a reserved SQL word!";
	  }

DESCRIPTION

       Determine if words are reserved by ANSI/ISO SQL standard.

METHODS

       is_reserved( $word )
	   Returns a boolean indicating if $word is reserved by either "SQL:1992", "SQL:1999" or "SQL:2003".

       is_reserved_by_sql1992( $word )
	   Returns a boolean indicating if $word is reserved by "SQL:1992".

       is_reserved_by_sql1999( $word )
	   Returns a boolean indicating if $word is reserved by "SQL:1999".

       is_reserved_by_sql2003( $word )
	   Returns a boolean indicating if $word is reserved by "SQL:2003".

       reserved_by( $word )
	   Returns a list with SQL standards that reserves $word.

       words
	   Returns a list with all reserved words.

EXPORTS

       Nothing by default. Following subroutines can be exported:

       is_reserved
       is_reserved_by_sql1992
       is_reserved_by_sql1999
       is_reserved_by_sql2003
       reserved_by
       words

SEE ALSO

       SQL::ReservedWords::DB2

       SQL::ReservedWords::MySQL

       SQL::ReservedWords::ODBC

       SQL::ReservedWords::Oracle

       SQL::ReservedWords::PostgreSQL

       SQL::ReservedWords::SQLite

       SQL::ReservedWords::SQLServer

       ISO/IEC 9075:1992 Database languages -- SQL

       ISO/IEC 9075-2:1999 Database languages -- SQL -- Part 2: Foundation (SQL/Foundation)

       ISO/IEC 9075-2:2003 Database languages -- SQL -- Part 2: Foundation (SQL/Foundation)

AUTHOR

       Christian Hansen "chansen@cpan.org"

COPYRIGHT

       This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.8.8							    2008-03-28						   SQL::ReservedWords(3pm)
All times are GMT -4. The time now is 03:36 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy