4

I'm using sequel pro to select data from several tables. There are two things I need to do that seem to need a loop of some kind. I have never used any form of iteration in sql and can't find a beginners-level resource to learn from.

Can anyone suggest how to do the following two tasks, or suggest a tutorial where I can learn the fundamentals and figure it out from there:

Task 1: Go through a version history table, find the relevant history record for a given id that applied at a given date, and select the value from that record. The form of the history table is:

  • id, Item_id, version-created_at, value

eg

  • 1, 123, 2014-05-01, 754
  • 2, 456, 2014-05-10, 333
  • 3, 123, 2014-05-27, 709

and I need to find what the value of item 123 was on the date 2014-05-25 (ie I need to find record id=1 and value = 754 because that is the most recent version for item 123 created prior to my target date.

So I figure I need to run through the table looking for item 123 and comparing dates of those records. But I don't know how to deal with the iteration of moving from one record to the next and comparing them.

Task 2: Go through a single text field that contains a number of product id and matching product prices in a string, and find the id of the product with the lowest price. Form of the string is a series of pairs of price "p" and id "i", in random order, like this:

" - :p: 99.8 :i: 3 - :p: 59.0 :i: 5 - :p: 109.8 :i: 18 - :p: 82.45 :i: 46 "

and in this example I need to find "5", being the id of the product with the lowest price $59.

So I figure I need to step through each of the p/i sets, maybe by counting characters, but I have no idea how to iterate through and compare to find the best price.

A little help would go a long way.

Thanks.

AJDncnsn
  • 53
  • 1
  • 5

1 Answers1

0

For first answer you can do something like this:-

SELECT value FROM history where id = 123 AND version-created_at = '2014-05-01';

and for another task you must try this at front end rather than at back end.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • Sorry, that doesn't work. I can't specify what the creation date equals because there are other items other than item 123 where the dates will be different. For each item I need to find the version that was live on a given date, and that is the one with the latest date for that item number that is less than the given date. That can't be done by looking at each row in isolation. – AJDncnsn May 29 '14 at 23:03