How to return a single row of data-CodePudding

The following query returns two rows. A PIDM (number) and two different sortest_test_date values.

319824|21-MAR-18
319824|18-APR-18

I would like the query to return a single row containing the PIDM and the max sortest_test_date. How do I accomplish this?

select distinct a.sortest_pidm pidm, max(a.sortest_test_date) max_test_date
from sortest a
where (a.sortest_tesc_code = 'ACC1' or a.sortest_tesc_code = 'ACC2')
and a.sortest_pidm = (select distinct sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC1' and b.sortest_test_score >= 81)
and a.sortest_pidm = (select distinct sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC2' and b.sortest_test_score >= 95)
and a.sortest_pidm = 319824
group by a.sortest_pidm, a.sortest_test_date;

CodePudding user response：

Basically, you need to remove the date from the group by. You can also make other improvements to the query as well.

select a.sortest_pidm pidm, max(a.sortest_test_date) max_test_date
from sortest a
where a.sortest_tesc_code in ('ACC1', 'ACC2') and
      a.sortest_pidm = (select sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC1' and b.sortest_test_score >= 81
                       ) and
      a.sortest_pidm = (select sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC2' and b.sortest_test_score >= 95) and
      a.sortest_pidm = 319824
group by a.sortest_pidm;

Basically, there is no reason to have distinct anywhere in the query. I suspect that the subqueries can be improved as well (say by using window functions), but your question doesn't provide enough information to make specific suggestions.

CodePudding user response：

Use TOP 1 (SQL Server) in select statement or ROWNUM < 2 (Oracle) in where clause to get top row.

Advice: Use sub-queries:

select distinct TOP 1 a.sortest_pidm pidm, max(a.sortest_test_date) max_test_date
from sortest a
where (a.sortest_tesc_code = 'ACC1' or a.sortest_tesc_code = 'ACC2')
and pidm in (select sortest_pidm from sortest b where b.sortest_tesc_code = 'ACC1' and b.sortest_test_score >= 81)
and pidm in (select sortest_pidm from sortest c where c.sortest_tesc_code = 'ACC2' and c.sortest_test_score >= 95)
order by max_test_date desc

CodePudding user response：

You could use this link enter link description here and order you results descending by the column containing the max statement.