CODEWITHSUNDEEP
mysql
Amy
Amy

Reputation: 193

MySQL Searching for nearest less than numerical value

I'm new to SQL, and I can't seem to find this answered anywhere else. I'm working on a project that requires me to convert between two different grids, which has been fairly straightforward in Excel. I have 2 tables of these forms (filled in first few rows for reference):

JDAY   TL1   TL2   TL3
1.5    148.2 147.3 146.4
2.5     150.2 150.1 150.0
3.5    151.1 150.7 150.3

Layer  Elevation
2      150.9
3      149.9
4      148.9
5      147.9
6      146.9
7      145.9

In Table1, the values under TL1, TL2, TL3 are elevations that will correspond to the Elevation column in Table2. I want to output a view like this:

JDAY   TL1   TL2   TL3
1.5    5     6     7
2.5    3     3     3
3.5    2     3     3

I need to search for the nearest less than elevation in Table2 for each value in Table1, and ouput the corresponding Layer number. I'm not sure how to do this since the elevations aren't exact matches. This works well using the vlookup() function in Excel. Another thing to note is that this is a small piece of the data...there are actually 15 TL columns and 365 JDAY values.

Upvotes: 1

Views: 1807

Answers (3)

troelskn
troelskn

Reputation: 117487

Firstly, your table1 should probably be normalised a bit:

create table measurements (
  jday date not null,
  measure varchar(5) not null,
  value decimal(6,2) not null,
  primary key (jday, measure)
);

So your dataset would be represented as:

jday       measure value
2012-05-01 TL1      148.2
2012-05-01 TL2      147.3
2012-05-01 TL3      146.4
...

(I assume that your JDAY column holds a date?)

Now, for your question, since you're using MySql, you can combine orderby, group by and subselects to get the result you're after. There are other ways to arrive at the same result, but I think this is fairly readable:

select * from (
  select m.*
  from measurements m
  join layers l
  where l.elevation <= m.value
  order by l.elevation desc ) x
group by jday, measure
order by jday, measure

Upvotes: 0

Neil Hampton
Neil Hampton

Reputation: 1883

Given the small number of rows involved the following could work:

SELECT A.JDAY,
       (select max(layer) from table2 where elevation<=A.tl1) as tl1,
       (select max(layer) from table2 where elevation<=A.tl2) as tl2,
       (select max(layer) from table2 where elevation<=A.tl3) as tl3,
       ...
FROM table1 AS A

Upvotes: 1

Marc B
Marc B

Reputation: 360672

It'll be ugly, but something like this might do:

SELECT JDAY, MAX(TL1) AS tl1, MAX(TL2) AS tl2, ...
FROM (
    SELECT JDAY, othertable.Layer AS TL1, othertable.Layer AS TL2, etc...
    FROM maintable
    INNER JOIN othertable AS tl1 ON maintable.TL1 < othertable.TL1
    INNER JOIN othertable AS tl2 ON maintable.TL2 < othertable.TL2
    etc...
) AS subquery

basically, the inner query gets all joined records where the othertable elevations are LESS than the maintable's. Then the other query selects the MAX() of each field, which should be the values you're looking for.

Upvotes: 2

Related Questions