# ‌How to select rows with minimum value in each group based on first column as ID?

I have a file looks like this :

``````1   7.8e-12
1   7.8e-12
1   1.0e-11
2   9.3e-13
2   3.5e-12
2   3.5e-10
2   3.1e-9
3   3.0e-11
3   3.0e-11
3   1.7e-08
``````

For every value in column one, I want to select "all rows" having minimum value in column 2 and group by column one. So the desired output is:

`````` 1   7.8e-12
1   7.8e-12
2   9.3e-13
3   3.0e-11
3   3.0e-11
``````

Any idea how to do this?

• @ Theophrastus, yes I knew about this but I want to keep the duplicate rows! – Anna1364 Feb 1 '18 at 20:23
• Anna, you’ll get (more) useful answers if you provide sample data that represents your actual data. – Jeff Schaller Feb 1 '18 at 21:19
• `1.7e-11` is smaller than `5.1e-11` – glenn jackman Feb 1 '18 at 21:27

One approach would be to sort in ascending order, then note the first col2 value for each col1 and print if the current col2 value is equal to it:

``````sort -k1,1n -k2,2g file | awk '!a[\$1] {a[\$1] = \$2} \$2 == a[\$1]'
1   7.8e-12
1   7.8e-12
2   9.3e-13
3   3.0e-11
3   3.0e-11
``````
• Thanks @steeldriver, this is doing the job for the example here, but my real numbers in my file are 7.8e-12,7.8e-12,1.0e-11,1.0e-11,3.9e-11 and this code does not select the smallest one! It selected rows with 1.0e-11, but 7.8e-12 is indeed smaller!. do you know what might be wrong? – Anna1364 Feb 1 '18 at 21:17
• @Anna1364 AFAIK `sort -n` does not recognize exponential (scientific) notation - if you have the GNU vairant, you can try `sort -g` (`--general-numeric-sort`) instead – steeldriver Feb 1 '18 at 21:21
• thanks a lot @steeldriver, now it works perfectly. Sorry for the confusion! – Anna1364 Feb 1 '18 at 21:43

This should handle numbers in scientific notation:

``````awk '
NR == FNR {
if (!(\$1 in min) || \$2 < min[\$1])
min[\$1] = \$2
next
}
\$2 == min[\$1]
' file file
``````

We process the file twice: once to find the min value for each key, then to output lines with that min value.

With GNU tools and complex as you need duplicated values!

``````grep -F "\$(datamash -W -g1 min 2 <infile | \
sed 's/\([^\.][1-9]\)e/\1.0e/')" <(sed 's/ \+/\t/' infile)
``````

if only minimum values were concerned, below was just enough with `datamash`.

``````datamash -W -g1 min 2 <infile
``````

Just to round out the text-processing based answers, here's a way to do this with PostgreSQL:

First, pre-process the file to convert it to CSV for easier import:

``````awk -v OFS=, '\$1=\$1' file.txt > file.csv
``````

Then create a temp table in PostgreSQL like so:

``````create temp table x (id int, bignum float);
``````

Copy the CSV into it:

``````\copy x from file.csv with (format csv)
``````

And query the temp table for the results you want:

``````select id, bignum
from (
select
*,
rank() over (partition by id order by bignum)
as rank
from x
) as sqlrequiresthisalias
where rank = 1;
``````

Results:

`````` id | bignum
----+---------
1 | 7.8e-12
1 | 7.8e-12
2 | 9.3e-13
3 |   3e-11
3 |   3e-11
(5 rows)
``````

GNU `awk` solution:

``````awk 'BEGIN{ PROCINFO["sorted_in"] = "@val_num_asc" }
{ a[\$1][++c] = \$2 }
END{
for (i in a) {
prev = 0;
for (j in a[i]) {
v = a[i][j]; if (prev && v != prev) continue;
print i, v; prev = v
}
}
}' file
``````

The output:

``````1 7.8e-12
1 7.8e-12
2 9.3e-13
3 3.0e-11
3 3.0e-11
``````