The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi all SQL Anywhere 8.0

I am using the following SQL successfully

SELECT cr_no, dated, height, weight FROM malaviya_vitals WHERE cr_no IN ( 
SELECT cr_no FROM mal_diagnosis WHERE mal_diagnosis.diag_code LIKE 
M05.%'  ) order by cr_no, dated; 

with a result set of 6605 rows

However I want to refine the result set a little more to get only the weight and height data of this group of patients only the first time the patient was seen. [dated = MIN (dated)] Need help to construct the exact syntax (should lead to a total of around 1215 rows = no. of patients with diag_code M05.---)

Any help will be appreciated

Regards

asked 15 Feb '13, 06:54

gogia7's gravatar image

gogia7
16112
accept rate: 0%

edited 15 Feb '13, 09:23

Breck%20Carter's gravatar image

Breck Carter
26.6k418576824


Try adding the following subquery to the outer WHERE clause:

AND dated = ( SELECT MIN( t.dated )
              FROM malaviya_vitals AS t
              WHERE t.cr_no = malaviya_vitals.cr_no )
permanent link

answered 15 Feb '13, 15:24

Graham%20Hurst's gravatar image

Graham Hurst
2.6k11843
accept rate: 28%

You could also try: SELECT cr_no, min(dated) as theDated, height, weight FROM malaviya_vitals WHERE cr_no IN ( SELECT cr_no FROM mal_diagnosis WHERE mal_diagnosis.diag_code LIKE ''%M05.%'' ) group by cr_no, height, weight order by cr_no, theDated

This will create a worktable as part of the query optimisation, but if your caching is sufficient and fast enough, your performance should be good too.

permanent link

answered 17 Feb '13, 23:36

Liam's gravatar image

Liam
36191118
accept rate: 0%

1

I think that gives a row for the earliest date of each unique (cr_no, height, weight) combination, rather than only a row for the first time each patient was seen.

(19 Feb '13, 10:56) Graham Hurst
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 15 Feb '13, 06:54

question was seen: 577 times

last updated: 19 Feb '13, 10:58