SQL Server SQL Query to only output datecolumn when other columns changed [closed]

x7rlezfr 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(104)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 22 hours ago.
Improve this question

The following table is given:

FQDN	content	Syncdate
FQDN1	content1	2023-01-01
FQDN1	content1	2023-01-02
FQDN1	content2	2023-01-03
FQDN1	content2	2023-01-04
FQDN1	content1	2023-01-05
FQDN2	content3	2023-01-01
FQDN2	content3	2023-01-02
FQDN2	content4	2023-01-03
FQDN2	content4	2023-01-04
FQDN2	content3	2023-01-05

As an output of my SQL-query I need the FQDN, the content and the date ONLY when the combination of FQDN and the content changes.

E.g.:

FQDN1;content1,2023-01-01
FQDN1;content2,2023-01-03
FQDN1;content1,2023-01-05
FQDN2;content3,2023-01-01
FQDN2;content4,2023-01-03
FQDN2;content3,2023-01-05

How can I achieve this in SQL Server?

I tried:

with cte as (
SELECT 

       ROW_NUMBER() OVER(PARTITION BY FQDN, content
       ORDER BY FQDN, content, Syncdate DESC) AS "RowNumber", 

FQDN, content, Syncdate
FROM tbl

)

select * from cte where RowNumber = '1'

But with this, I get only "2023-01-05" for FQDN1 in combination with content1.

sql-server

来源：https://stackoverflow.com/questions/76809861/sql-query-to-only-output-datecolumn-when-other-columns-changed

1条答案

按热度按时间

avkwfej41#

You can try

SELECT FQDN, content, Syncdate from
(SELECT FQDN, content, Syncdate, ROW_NUMBER() OVER(PARTITION BY FQDN, content) row_num FROM tbl)
WHERE row_num=1

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL Query to only output datecolumn when other columns changed [closed]

1条答案

相关问题

热门标签

最新问答