如何在SQL Server中编写以下查询 [英] How do I write the following Query in SQL Server

问题描述

这是场景

我有这样的表

OppID ModifyDate Old_Stage值New_Stage值



Opp_1 01-01-2013 Null Hold

Opp_1 05-11-2014 Hold Lost





我想要提取机会的最新修改日期。

注意：我需要将此表与其他人一起加入。

如何编写查询。请帮助!!

Here is the Scenario
I have table like this
OppID ModifyDate Old_Stage Value New_Stage Value

Opp_1 01-01-2013 Null Hold
Opp_1 05-11-2014 Hold Lost


I want to extract the latest Modify Date wrt Opportunity.
Note: I Need to Join this table with Others.
How can i write the query. Please Help !!

推荐答案

select * from Table order by ModifyDate desc 

将通过降序为您提供数据修改顺序。



你可以选择

Will give you data by descending order of modification.

you can choose to have

select top 1 * from Table order by ModifyDate desc  

获取最新修改日期。



其他方式是申请排名功能

to get latest modification date.

Other way is to apply Ranking function

select *,Row_Number() Over (order by ModifyDatedesc ) As LatestRecord from Table order by ModifyDate desc

然后您可以在1上过滤LatestRecord列，该列将保留最新日期。



您还可以查看这个

and then you can filter LatestRecord column on 1 which will hold latest date.

You can also check this

SELECT * FROM TABLENAME ORDER BY PRIMARYCOLUMNNAME DESC
FETCH FIRST 1 ROWS ONLY ;

但是这个选项只有在有主键的情况下才有效

列，否则其工作正常

BUT THIS OPTION IS VALID ONLY IF THERE IS A PRIMARY KEY
COLUMN,OTHERWISE IT WONT WORK

您的问题有点缺乏信息。

但我相信这样的事可能有用：

Your question is a bit short on information.
But I believe something like this may work:

select 
    opp.*,
    oppi.*
from Opportunity opp
inner join (
          select OppID, max(ModifyDate) ModifyDateMax
          from Opportunity
          group by OppID
    ) oppMaxDt
    on 
          opp.OppID = oppMaxDt.OppID
          and 
          opp.ModifyDate = oppMaxDt.ModifyDateMax 
--additional table join(s) can go here
inner join Opp_Info oppi
	on opp.OppID = oppi.[Opp ID]
;

note ：OppID和ModifyDate必须是唯一的，查询才能为每个OppID返回一条记录和最大的ModifyDate

note: OppID and ModifyDate need to be unique for the query to return one record for each OppID and max ModifyDate

Select OppID, ModifyDate ,Old_Stage_Value, New_Stage_Value from tablename
where ModifyDate in(Select max(ModifyDate) from tablename group by OppID)

它将显示最新的OppID数据

检查它是否有帮助

it will display latest OppID data
check it will help

这篇关于如何在SQL Server中编写以下查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！