1

I'll try to explain by example what I want to achieve in MySQL.

I have a table looking like this:

pricelist_id valid_from
1           1610665200000 //15 Jan 2021
2           1610751600000 //16 Jan 2021         
3           1610838000000 //17 Jan 2021

Values in column, valid_from, are essentially String values which were generated at some point in time by following Java code:

String.valueOf(System.currentTimeMillis())

My assignment is to pass any String value (in the format of a number, in this case long), generated in the same way as explained above and I need to extract single pricelist_id which belongs to the first lesser or equal value of valid_from in the table, in comparison with passed value of valid_from. It is hard to explain by words (and my English is just terrible), so I'll demonstrate.

If I pass a value which will represent Jan 18th, 1610924400000, I would need to get single pricelist_id value, in this case 3.

If I pass a value which will represent Jan 16th 17:15:00, 1610813700000, I would need to get single pricelist_id value, in this case 2.

If I pass a value which will represent Jan 25th, 1611529200000, I would need to get single pricelist_id value, in this case 3.

What I have so far, is this:

select  max(p.pricelist_id)
from pricelist p
where  (p.valid_from + 0) <= some_passed_value
order by p.valid_from, p.pricelist_id desc
Rick James
  • 135,179
  • 13
  • 127
  • 222
Wrapper
  • 794
  • 10
  • 25

2 Answers2

1

If you can rely on pricelist_id being in the exact same sort order as valid_from, then your solution using max() works.

Otherwise you can use LIMIT:

select  p.pricelist_id
from pricelist p
where  (p.valid_from + 0) <= some_passed_value
order by p.valid_from, p.pricelist_id desc
limit 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

Convert the string to number for ordering and for comparing Also use limit to get the higher value

select p.pricelist_id from pricelist p where CONVERT(p.valid_from, SIGNED) <= some_passed_value order by CONVERT(p.valid_from, SIGNED), p.pricelist_id desc LIMIT 0, 1

Peter Chidiac
  • 11
  • 2