Remove duplicate rows from query based on value in one column

Question

I have a table (SQL Server) where I store the run status of all of our automated test cases and I'm trying to come up with an SQL query to retrieve the status per runid, but I'm running into some problems with it.

Example of data within the table:

KRUNID |      KTIME     |  FK_TC_ID  | NOPART | STATUS | ENV
-------+----------------+------------+--------+--------+-----
4180-2 | 20190109080000 |   TC0001   | 123456 | Passed | INT
4180-2 | 20190109080100 |   TC0002   | 123457 | Failed | INT
4180-2 | 20190109080200 |   TC0003   | 123458 | Passed | INT
4180-2 | 20190109080400 |   TC0002   | 123459 | Passed | INT

Right now, I have this query (the join statements are used to display the actual test case name and business domain):

SELECT KRUNID, TD_NAME, TS_NAME, FK_TC_ID, TC_DISPLAYNAME, NOPARTENAIRE,  
ENV, STATUS FROM RU_RUNSTATUS 
INNER JOIN TC_TESTCASES ON K_TC_ID = FK_TC_ID 
INNER JOIN TS_TCSUBDOMAINS ON K_TS_ID = FK_TS_ID 
INNER JOIN TD_TCDOMAINS on K_TD_ID = FK_TD_ID 
WHERE KRUNID = '418-2' 
ORDER BY FK_TS_ID, K_TC_ID

The query is basic and it works fine except that I will have 2 lines for TC0002 when I only want to have the last one based on KTIME (for various reasons I don't want to filter based on STATUS).

I haven't found the right way to modify my query to get the result I want. How can I do that?

Thanks

Use `MAX()` function and `GROUP BY` clause – Ilyes Jan 09 '19 at 07:48 — Ilyes, Jan 09 '19 at 07:48

Sanpas · Accepted Answer · 2019-01-09T07:53:59.097

0

I think this article can be respond : Get top 1 row of each group

Look of your query with limit partioned by FK_TC_ID and ordered by KTIME

;WITH cte AS
(
SELECT FK_TS_ID, KRUNID, TD_NAME, TS_NAME, FK_TC_ID , TC_DISPLAYNAME, NOPARTENAIRE,  
ENV, STATUS, ROW_NUMBER() OVER (PARTITION BY FK_TC_ID  ORDER BY KTIME DESC) AS rn
FROM RU_RUNSTATUS 
INNER JOIN TC_TESTCASES ON K_TC_ID = FK_TC_ID 
INNER JOIN TS_TCSUBDOMAINS ON K_TS_ID = FK_TS_ID 
INNER JOIN TD_TCDOMAINS on K_TD_ID = FK_TD_ID 
WHERE KRUNID = '418-2' 
)
SELECT *
FROM cte
WHERE rn = 1
ORDER BY FK_TS_ID, K_TC_ID

edited Jan 09 '19 at 07:53

answered Jan 09 '19 at 07:48

Sanpas

1,170
10
29

1

Thanks for your quick answer. However, when I try your query, I get the following error message: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. If I remove the order by clause, the table is empty – Mu21 Jan 09 '19 at 07:51
1

The error message is obvious. Simply move the order by to the very end. – Salman A Jan 09 '19 at 07:52
Yes sorry i didn't have test this, Salman A have update this and it can be work. Let me know if you have excepted result. – Sanpas Jan 09 '19 at 07:53
At the very end of the query? Then I get an invalid column name error message. – Mu21 Jan 09 '19 at 07:53
@Muriel try with this update i have adding FK_TS_ID on sub query – Sanpas Jan 09 '19 at 07:54

Remove duplicate rows from query based on value in one column

1 Answers1