Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Unanswered: Mysql Query Question

    Hello, I have a MySQL problem to solve but cant manage it.

    I have a measurement tble acccording to dates for some speciemens that uppended irregularly (means there is no measurements in stable periods for all of them, just has many measurements all of them) The structure of it :

    |----------------------------------------------------------|
    | rec_id -| sample_id | measurement_date | measured_value |
    |--------|-----------|-------------------|-----------------|
    | 123456 | EX01.......| 2013-01-01...........| 75,67...............|
    | 123457 | EX02.......| 2013-01-01...........| 67.45...............|
    | 123458 | EX03.......| 2013-01-04...........| 83.75...............|
    | 123459 | EX01.......| 2013-02-03...........| 94.72...............|
    | .......... | .............| ...........................| ......................|
    |----------------------------------------------------------|

    I need a result table like that :
    specimen_id, last measurement dates and value, first measurement date and value, and last 14 measured dates and values.
    |-----------------------------------------------------------------------------------------------------|
    | sample_id | last_MD | Last _MV | L-1_MD | L-1_MV | .......... | L-14_MD | L-14_MV | First_MD | First_MV|
    |------- ---|----- ---|----------|---------|---- ----|........... |-------- -|---- -----|---------|---------|


    I will greatly appreciate any feedback,
    Thank you.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Use "GROUP BY sample_id" and CASE expressions.

    If the last_MD was not known,
    use a subquery to know the last_MD
    then join the subquery with the original table.


    By the way,
    is last_MD the value for each sample_id?
    or is it the last value of whole rows in the table?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your sample data were too small.
    I want at least 15 or 16 rows for each sample_id.
    Because, I want to know the differences of last_MD , L-1_MD , L-14_MD , First_MD for each sample_id.

    And, please publish the expected results from your sample data.

    Other question:
    (1) Are there measurement_dates having same month for a sample_id?
    If there are multiple measurement_dates for a month, which value should take?
    Note: I guessed that you might want to know values for each L-n_MD and L-n_MV months(n are 1 to 14.),
    because measurement_dates for EX01 were '2013-01-01' and '2013-02-03' which were different months.
    Last edited by tonkuma; 06-07-13 at 00:27. Reason: Add Note.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •