Multiple Entries That Meet Criteria

Question:
How can I pull just the first match to a query? We have a database of jobs in process:

jobno 12345op# 10 shear %complete 100op# 20 punch % complete 90op# 30 deburr %complete 0op# 40 timesave %complete 0jobno 67890op# 10 shear %complete 100op# 20 punch % complete 100op# 30 deburr %complete 90op# 40 timesave %complete 0

I want my query to pull just the first occurrence of WHERE %complete < 100. Right now, my results return op#20, 30, and 40 for jobno 12345 and op#30 and 40 for jobno 67890. I want the results to be just op#20 for jobno 12345 and op#30 for jobno 67890.

Answer:
For a table called TestTable with the columns JobNo, OP, and PercentComplete and the above data, this query returns the JobNo and the maximum PercentComplete entry where the PercentComplete is less than 100:

SELECT JobNo,  MAX(PercentComplete) AS PercentComplete FROM TestTableWHERE PercentComplete < 100GROUP BY JobNo

Returns this data set:

JobNo      PercentComplete ---------- --------------- 12345      9067890      90

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: