select max(ObjectGUID),AlarmObjectID from [alarmdb].[dbo].[PowerEnvAlarm]
where Cancel_Time is null
GROUP BY AlarmObjectID,NMAlarmID
本想用这个语句找出表中 AlarmObjectID,NMAlarmID 相同的一条记录的
无奈ObjectGUID 是varchar类型 ,请大伙给个办法!
where Cancel_Time is null
GROUP BY AlarmObjectID,NMAlarmID
本想用这个语句找出表中 AlarmObjectID,NMAlarmID 相同的一条记录的
无奈ObjectGUID 是varchar类型 ,请大伙给个办法!
where Cancel_Time is null and not exists(select 1 from powerenvalarm where AlarmObjectID=a.AlarmObjectID and NMAlarmID=a.NMAlarmID and ObjectGUID>a.ObjectGUID)
其实max 也可以用的,字符串类型的max值就是排序中最大的值.
from [alarmdb].[dbo].[PowerEnvAlarm]
where Cancel_Time is null
GROUP BY AlarmObjectID,NMAlarmID
having count(*)>1
where Cancel_Time is null
and ObjectGUID=(select max(ObjectGUID) from powerenvalarm
where AlarmObjectID=a.AlarmObjectID and NMAlarmID=a.NMAlarmID)
select max(ObjectGUID),AlarmObjectID from [alarmdb].[dbo].[PowerEnvAlarm]
where Cancel_Time is null and ObjectGUID=ltrim(AlarmObjectID)
GROUP BY AlarmObjectID,NMAlarmID
select * from (
select *,row_number() over (partition by AlarmObjectID,NMAlarmID order by ObjectGUID) AS NUM_ID
from [alarmdb].[dbo].[PowerEnvAlarm]
where Cancel_Time is null) a
where NUM_ID=1