How to filter a table by two columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to filter a table by two columns
# 1  
Old 09-09-2010
MySQL How to filter a table by two columns

Dear Forum,

I would like to know how could I found every result from one column at a table based at two table.

Exemplo:

Table:
Code:
Red    4
Red    5
Red    10
Black   33
Black   44
Black   5
Green  2
Green 55
Green 78

I would like to have every color with the lower result from the second column:
Code:
Red   4
Black 5
Green 2

Thank you very much!

Leandro

Last edited by Scott; 09-09-2010 at 05:19 PM.. Reason: Added code tags
# 2  
Old 09-09-2010
Try:
Code:
awk '!a[$1]{a[$1]=$2} $2<a[$1]{a[$1]=$2} END{for(i in a)print i, a[i]}' file

This User Gave Thanks to Franklin52 For This Post:
# 3  
Old 09-09-2010
Another way, but with two commands:
Code:
# myFile="TestFile.txt"
# numDiff=`cut -f1 -d" " "${myFile}" | sort | uniq | wc -l`
# sort -n -k2 "${myFile}" | head -"${numDiff}"
Green 2
Red 4
Black 5

## Or in one!
# sort -n -k2 "${myFile}" | head -`cut -f1 -d" " "${myFile}" | sort | uniq | wc -l`

Regards!
This User Gave Thanks to felipe.vinturin For This Post:
# 4  
Old 09-09-2010
Dear Sir Franklin52,

Thank you very much! How can I change the code for print more columns (like, column 3, 4 and 5?)

Thank you again,

Best Regards, Leandro

---------- Post updated at 02:19 PM ---------- Previous update was at 01:34 PM ----------

Dear Felipe.vinturin,

I tried to use your code, but I don't know why when I use for larger data it prints repetitive objetcs from the first column. My file has more columns, could be this?

Thank you very much!

Best Regards, Leandro
# 5  
Old 09-09-2010
Hi,

Maybe, if your file columns does not match the ones you put here!

In my command, I sort using the second column, if it is not the column with the number, it will print repetitive data.

Can you post an example of your file? With all columns?

Regards.
# 6  
Old 09-09-2010
Quote:
Originally Posted by lColli
Dear Sir Franklin52,

Thank you very much! How can I change the code for print more columns (like, column 3, 4 and 5?)

Thank you again,

Best Regards, Leandro
Post an example of the input file and the desired output.
# 7  
Old 09-09-2010
Dear Felipe,

Here is the data and I would like just the lines in red!

Thanks again!

Best Regard, Leandro

Tag Database rank order Accession UniGene cluster number
Code:
AAAAAAAAAC 46 _ Hs.621716
AAAAAAAAAC 46 AA636074 _
AAAAAAAAAC 77 AI861786 _
AAAAAAAAAC 77 BE536886 _
AAAAAAAAAC 77 BE548727 _
AAAAAAAAAC 77 BF691443 _
AAAAAAAAAC 79 BF754052 _
AAAAAAAAAC 80 BI004369 _
AAAAAAAAAC 86 BU535655 _
AAAAAAAAAC 86 BU539770 _
AAAAAAAAAC 86 BX111054 _
AAAAAAAAAC 86 BX643752 _
AAAAAAAAAC 86 CA390277 _
AAAAAAAAAC 94 CD522477 _
AAAAAAAAAC 94 DR159089 _
AAAAAAAAAG 5 _ Hs.190440
AAAAAAAAAG 26 _ Hs.445936
AAAAAAAAAG 46 _ Hs.567437
AAAAAAAAAG 74 _ Hs.674701
AAAAAAAAAG 77 _ Hs.700965
AAAAAAAAAG 77 AI034458 _
AAAAAAAAAG 77 AV699522 _
AAAAAAAAAG 77 AV719142 _
AAAAAAAAAG 79 AW150843 _
AAAAAAAAAG 80 AW801953 _
AAAAAAAAAG 80 AW857024 _
AAAAAAAAAG 86 BC033980 Hs.721040
AAAAAAAAAG 86 BE072635 _
AAAAAAAAAG 86 BF104499 _
AAAAAAAAAG 86 BF897128 _
AAAAAAAAAG 93 BG537298 _
AAAAAAAAAG 93 BG706646 _
AAAAAAAAAG 94 BI025564 _
AAAAAAAAAG 94 BX425052 _
AAAAAAAAAG 100 F24242 _

---------- Post updated at 02:37 PM ---------- Previous update was at 02:37 PM ----------

Dear Frank,

The output would be

Code:
AAAAAAAAAC 46 _ Hs.621716
AAAAAAAAAG 5 _ Hs.190440

The awk code gives me:
Code:
AAAAAAAAAC 46
AAAAAAAAAG 5

Thank you!

Last edited by Scott; 09-09-2010 at 05:20 PM.. Reason: Please use code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Filter table of different length

Dear Forum, I have to filter (e.g. PF=0.8) a text file according to some measured and recorded values for different fields (sensor). The files can be large and the recorded data points (ID) could differ in length. I have worked out a solution but it is very messy and not flexible. Does anybody... (4 Replies)
Discussion started by: GDC
4 Replies

2. Shell Programming and Scripting

Send Data to MySQL Table Columns

I have two scripts, each script reads an individual data file and copies specific lines of data and sends to MySQL table. Only difference is, each script sends data to a separate column on the same DB. I want to use one script to populate DB table and have data look horizontal, with no overlapping.... (3 Replies)
Discussion started by: SysAdminRialto
3 Replies

3. UNIX for Dummies Questions & Answers

Solaris - Filter columns in text file and adding new column

Hello, I am very now to this, hope you can help, I am looking into editing a file in Solaris, with dinamic collums (lenght varies) and I need 2 things to be made, the fist is to filter the first column and third column from the file bellow file.txt, and create a new file with the 2 filtered... (8 Replies)
Discussion started by: jpbastos
8 Replies

4. Shell Programming and Scripting

awk filter by columns of file csv

Hi, I would like extract some lines from file csv using awk , below the example: I have the file test.csv with in content below. FLUSSO;COD;DATA_LAV;ESITO ULL;78;17/09/2013;OL ULL;45;05/09/2013;Apertura NP;45;13/09/2013;Riallineamento ULLNP;78;17/09/2013;OL NPG;14;12/09/2013;AperturaTK... (6 Replies)
Discussion started by: giankan
6 Replies

5. Shell Programming and Scripting

Filter rows from table

Hi , I need to filter input file according to following All rows with the following conditions should be removed 1) If in a row, the number of 'N's starting col 2 exceeds 2 (3 or more) OR 2) If a row is duplicated with the same value, starting col 2, A value 'N' is considered missing... (1 Reply)
Discussion started by: newbie83
1 Replies

6. Shell Programming and Scripting

Export table of 50 columns

Dear all, I have a big table of 50 columns and more then 100, 000 rows in mysql. Could you please help me that how I can export it as .csv or . txt that I can open it in MS excel????? OR how can I export the specific columns of the table??? Thanks AAWT (5 Replies)
Discussion started by: AAWT
5 Replies

7. Shell Programming and Scripting

awk filter by occurence of at least two columns

Hi, Using AWK script I want to pick those rows that has AT LEAST TWO columns EACH has a count >=3. i.e. two conditions: at least two columns, each of which has a count at least 3. There must be a simple way to do this job, but could not figure it out by myself. Input file (thousand of... (3 Replies)
Discussion started by: yifangt
3 Replies

8. Programming

Creating a table like format with rows and columns

I have few files which have two columns in each. like e2 1 1 2694 2 4 2485 3 2 2098 5 1 2079 6 5 2022 9 4 1734 11 5 1585 13 2 1461 18 1 1092 21 2 1019 24 1 915 25 3 907 27 1 891 28 3 890 34 1 748 39 1 700 (1 Reply)
Discussion started by: kamuju
1 Replies

9. Shell Programming and Scripting

need to insert two columns (or two feilds) at the begining of the table

Hey guys/gals, So here is what i'm starting with...its a Solaris patch diag output. bash-3.0# pca -l Using /var/tmp/patchdiag.xref from Oct/20/08 Host: seiftsx4140 (SunOS 5.10/Generic_127128-11/i386/i86pc) List: missing Patch IR CR RSB Age Synopsis ------ -- - -- --- ---... (3 Replies)
Discussion started by: zeekblack
3 Replies

10. Solaris

Comparing the common columns of a table in two files

Hi, I have two text files.The first and the 2nd file have data in the same format For e.g. The first file has BOOKS COUNT: 40 BOOKS AUTHOR1 SUM:1018 MAX:47 MIN:1 AVG:25.45 BOOKS AUTHOR3 SUM:181 MAX:48 MIN:3 AVG:18.1 Note:Read it as Table columnname sum(column) max(column) min(column)... (1 Reply)
Discussion started by: ragavhere
1 Replies
Login or Register to Ask a Question