# JuliaDB filter: select one of multiple rows

**URL:** https://discourse.julialang.org/t/juliadb-filter-select-one-of-multiple-rows/9617
**Category:** New to Julia
**Created:** [March 9, 2018, 7:10pm UTC](https://discourse.julialang.org/t/juliadb-filter-select-one-of-multiple-rows/9617 "2018-03-09T19:10:32Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![MaximilianJHuber](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/maximilianjhuber/32/2579_2.png) [@MaximilianJHuber](https://discourse.julialang.org/u/MaximilianJHuber)
#### Post date: [March 9, 2018, 7:10pm UTC](https://discourse.julialang.org/t/juliadb-filter-select-one-of-multiple-rows/9617/1 "2018-03-09T19:10:32Z")

</div>

I have a table with three keys: NR=Int64, DT1=Date, DT2=Date, and DT2 is always before DT1.

I want to `filter` the table with two conditions:

- DT1=Date(2017, 3, 31)
- choose the newest by DT2 (i.e. closest to DT1) of possibly multiple entries that have the same NR and DT1.

Very similar to a [question](https://stackoverflow.com/questions/10452940/sql-select-newest-records-that-have-distinct-name-column) on StackExchange, just that I want NR to be the group-by variable.

While this is easily formulated as a SQL query, my implementation in [JuliaDB](https://github.com/JuliaComputing/JuliaDB.jl) is complicated and not exaclty fast.  
I filter the correct date, then figure out the correct DT2 per NR, cast it into a Dictionary, and finally filter the entries by the correct DT2:

```julia
select_date = filter(d -> d == Date(2017, 3, 31), data_table, select = :DT1)
correct_DT2 = collect(groupby(@NT(DT2=d -> maximum(d)), select_date , :NR, select=:DT2))

dict = Dict{Int64, Date}()
for i in 1:length(correct_DT2)
   dict[correct_DT2[i].NR] = correct_DT2[i].DT2
end

filter(row -> row.DT2 == dict[row.NR], select_date, select = (:NR, :DT2))

```

It seems to me that the [filter](http://juliadb.org/latest/api/selection.html#Filter-1) function has less functionality than SQL, for example the `GROUP BY` qualifier.

Any thought on how to do it better?