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
