# Query newest entry with Query.jl
**URL:** https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385
**Category:** Data
**Tags:** question
**Created:** [July 20, 2020, 4:42pm UTC](https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385 "2020-07-20T16:42:53Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@ohmsweetohm1](https://discourse.julialang.org/u/ohmsweetohm1)
#### Post date: [July 20, 2020, 4:42pm UTC](https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385/1 "2020-07-20T16:42:53Z")
I have a table with multiple entries.
For each entry I have the timestamp it was fetched and the timestamp it represents, e.g. forecasts for a specific timestamp computed at different times.
I would like to query the newest entry for a given vector of timestamps.
Is there a way to do this with Query.jl?
In SQL I would do something like:
```nohighlight
SELECT MAX(t_fetch), *
FROM data
WHERE date
BETWEEN t_from AND t_to
GROUP BY date ORDER BY date ASC
```
With the following i get all forecasts that where made for the given `timestamp`
```julia
df2 = df |> @query(d, begin
@orderby d.date, descending(d.fetch)
@where d.date == timestamp
@select d
end) |> DataFrame
```
from this dataframe I would have to select the first element and do this for all timestamps in the given vector.
```julia
df3 = df2 |> @take(1) |> DataFrame
```
Can I combine this with two nested queries somehow?
---
### Author:  [@ohmsweetohm1](https://discourse.julialang.org/u/ohmsweetohm1)
#### Post date: [July 22, 2020, 12:40pm UTC](https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385/2 "2020-07-22T12:40:44Z")
I have also tried:
```julia
df |>
@filter(_.date == time[1]) |>
@orderby_descending(_.fetch) |>
@take(1) |>
DataFrame
```
I would have to repeat this for all `t in time` and combine the result in a single `DataFrame`.
This is what I think I should do:
```julia
df |>
@groupby(_.date) |>
@orderby_descending(_.fetch) |>
@take(1) |>
@map(DataFrames.DataFrame(_)) |>
collect |> first
```
However, I get the wrong result. I now have every `fetch` for the first `date`.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [July 22, 2020, 1:02pm UTC](https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385/3 "2020-07-22T13:02:23Z")
in base DataFrames I think what you want is
```julia
ulia> df = DataFrame(a = [1, 1, 2, 2], b = rand(4))
4×2 DataFrame
│ Row │ a │ b │
│ │ Int64 │ Float64 │
├─────┼───────┼──────────┤
│ 1 │ 1 │ 0.319191 │
│ 2 │ 1 │ 0.400976 │
│ 3 │ 2 │ 0.919093 │
│ 4 │ 2 │ 0.507223 │
julia> sort!(df, [:a, :b])
4×2 DataFrame
│ Row │ a │ b │
│ │ Int64 │ Float64 │
├─────┼───────┼──────────┤
│ 1 │ 1 │ 0.319191 │
│ 2 │ 1 │ 0.400976 │
│ 3 │ 2 │ 0.507223 │
│ 4 │ 2 │ 0.919093 │
julia> combine(first, groupby(df, :a))
2×2 DataFrame
│ Row │ a │ b │
│ │ Int64 │ Float64 │
├─────┼───────┼──────────┤
│ 1 │ 1 │ 0.319191 │
│ 2 │ 2 │ 0.507223 │
```
But I’m not 100% sure thats what you want to do. Not sure how to do it with Query, sorry.
---
### Author:  [@ohmsweetohm1](https://discourse.julialang.org/u/ohmsweetohm1)
#### Post date: [July 22, 2020, 1:04pm UTC](https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385/4 "2020-07-22T13:04:07Z")
I found a working solution
```julia
dfq = vcat([
(
df |>
@filter(_.date == t) |>
@orderby_descending(_.fetch) |>
@take(1) |>
DataFrame
) for t in time
]...)
```
but it is not really elegant.
---
### Author:  [@ohmsweetohm1](https://discourse.julialang.org/u/ohmsweetohm1)
#### Post date: [July 22, 2020, 1:14pm UTC](https://discourse.julialang.org/t/query-newest-entry-with-query-jl/43385/5 "2020-07-22T13:14:43Z")
And with the solution from @pdeffebach
```julia
dfq2 =
df |>
x -> DataFrames.sort(x, ["fetch", "date"], rev = (true, false)) |>
x -> DataFrames.combine(first, DataFrames.groupby(x, "date")) |>
x -> Dataframes.filter("date" => x -> time[1] <= x <= time[end], x) |>
DataFrames.DataFrame
```