1

So i have an table with daily information of different accounts, a user wants to get the data of all accounts based on an date xxxx-xx-xx, but the latest information in some of these accounts are from some days earlier than the date the user want. How can i make an query that gets the information from the date the user want and also info that have the most close date from what he want. I hope i made myself clear, the database is in mysql.

EDIT:

bit more of explanation

SELECT * from `table` where date BETWEEN '2021-01-01' AND userinput

so the objective is make this query get the latest information, the one closest to the userinput, given the fact that there's multiple rows of the same account but with different dates, the only ones that matters are the ones closer to the date the user has given.

EDIT 2:

TABLE accounts:

id account_id account_info date
1 15 some info 1 2020-01-01
2 15 some info 2 2020-01-02
3 15 some info 3 2020-01-03
4 16 some info 4 2020-01-03
5 17 some info 1 2020-01-07

the table above shows in an simplified way how the table looks like, the user them wants to take the information of every account on the day 7 let's suppose, as we can see account 15 as info only until day 3, account 16 the same, only account 17 have info on day 7, so the query must get the info of account 15 and 16 only from day 3 (because in this case day 3 is the closest to day 7) while for account 17 from day 7.

SELECT * from accounts --> what's next? i have the user input [2021-01-07]
Bruno D.
  • 29
  • 7
  • Use `BETWEEN` with a range of dates around the user input date. – Barmar Apr 22 '21 at 23:45
  • i tried that but it returns the info of the older days too, i need only the most recent information – Bruno D. Apr 22 '21 at 23:48
  • 1
    See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql for how to filter it down to just the most recent for each account. – Barmar Apr 22 '21 at 23:52
  • can you give me an example based on my question? Thanks! – Bruno D. Apr 23 '21 at 00:03
  • Use the solution in Gordon's answer, it's simpler. – Barmar Apr 23 '21 at 00:08
  • 1
    `Order by... limit 1` seems even simpler to me. Anyway, Bruno, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Apr 23 '21 at 05:36
  • @Strawberry hello, i edited my question again to try giving a better visualization of what i mean, if still not that good tell me (i am still a newbie XD). Thanks. – Bruno D. Apr 23 '21 at 20:39

1 Answers1

1

Let me guess that your table has multiple rows for a given account along with a date for each row. If that is the case, you can use logic like this:

select t.*
from `table` t
where t.date = (select max(t2.date)
                from `table` t2
                where t2.account = t.account and t2.date <= ?
               );

For performance, you want an index on (account, date).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786