检索一列具有最大值的行

Question

我有这个查询：

select * 
from COMMISSION  
where dealer_id in (select dealer_id from DEALER where COM_NAME like 'abcdef')  

该查询返回两行，其中包括值为100、102的列STATUS_ID。我想检索具有STA_ID = 102的行。我该怎么做？请帮忙。

Answer 1

select top 1 id
from Commission
where dealer_id in (select dealer_id from DEALER where COM_NAME like 'abcdef')
order by Status_ID desc

Answer 2

你能试试这个吗

select * from COMMISSION  
where dealer_id in (select dealer_id from DEALER where COM_NAME like 'abcdef') 
And STA_ID in(
select max(STA_ID) from COMMISSION  
where dealer_id in (select dealer_id from DEALER where COM_NAME like 'abcdef'))

Answer 3

您可以使用max()聚合和关联子查询

select * from COMMISSION a 
where dealer_id in 
(select max(STATUS_ID) from DEALER b where a.dealer_id=b.dealer_id and 
COM_NAME like '%abcdef%') 

或者，您可以使用inner join和Max()聚合尝试以下操作

由于您的状态列在佣金表中，您可以尝试以下查询

select a.commisionid, max(STATUS_ID) 
from COMMISSION a
inner join DEALER b on a.dealer_id = b.dealer_id
where COM_NAME like '%abcdef%'
group by a.commisionid