devxlogo

Multiple Entries That Meet Criteria

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

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist