hi

Consider a table (ShogenValue) with following data:


Model_Id header_id Serial_no shogen_no si_val
4329 1 40001 A0001 1

4329 1 40001 B0001 2
4329 1 50300 B0001 3

4329 1 40001 C0001 4
4329 1 50300 C0001 5
4329 1 60001 C0001 6


Select
si_val
From
ShogenValue
Where
model_id = 4329
And
header_id = 1
And
serial_no = '60001'

Is it possible to write a modified version of above query which will bring the following result:

Model_Id header_id Serial_no shogen_no si_val
4329 1 40001 A0001 1
4329 1 50300 B0001 3
4329 1 60001 C0001 6

Meaning if a record exists for a particular shogen_no (Shogen_no C0001 has value corrosponding to '60001' in above query), pick it up, if it

does not exist (Shogen_no B0001 does not have value corrosponding to '60001' in above case) then pick a record with one level less serial

number ('50300' in above case) and so on.

Thanx
Omer Imtiaz

Use MAX to get the highest shogen_no for each Serial_no:

select serial_no, max(shogen_no) max_shogen_no
from shogen_value
where ...
group by serial_no;

Then join that to main query, e.g

select ...
from shogen_value
where (serial_no, shogen_no) in
(
select serial_no, max(shogen_no) max_shogen_no
from shogen_value
where ...
group by serial_no
);