How to return only 1 row if multiple duplicate rows and still return rows that are not duplicated?

Question

I have a tempting situation that looks like this:

RequestID | CreatedDate | HistoryStatus CF-0000001 | 8/26/2009 1:07:01 PM | For Review CF-0000001 | 8/26/2009 1:07:01 PM | Completed CF-0000112 | 8/26/2009 1:07:01 PM | For Review CF-0000113 | 8/26/2009 1:07:01 PM | For Review CF-0000114 | 8/26/2009 1:07:01 PM | Completed CF-0000115 | 8/26/2009 1:07:01 PM | Completed 

And how I would like the table to look like this:

 RequestID | CreatedDate | HistoryStatus CF-0000001 | 8/26/2009 1:07:01 PM | Completed CF-0000112 | 8/26/2009 1:07:01 PM | For Review CF-0000113 | 8/26/2009 1:07:01 PM | For Review CF-0000114 | 8/26/2009 1:07:01 PM | Completed CF-0000115 | 8/26/2009 1:07:01 PM | Completed 

those. duplicate CF-0000001 must be deleted.

How can I return, or should I say that I select only one row if there are several duplicate rows and still return rows that are not duplicated?

Answer 1

Try this if you want to display one of the repeating rows based on RequestID and CreatedDate and show the last HistoryStatus.

 with t as (select row_number()over(partition by RequestID,CreatedDate order by RequestID) as rnum,* from tbltmp) Select RequestID,CreatedDate,HistoryStatus from ta where rnum in (SELECT Max(rnum) FROM t GROUP BY RequestID,CreatedDate having t.RequestID=a.RequestID) 

or if you want to select one of the repeating rows, taking into account only CreateDate and show the last HistoryStatus, try the query below.

 with t as (select row_number()over(partition by CreatedDate order by RequestID) as rnum,* from tbltmp) Select RequestID,CreatedDate,HistoryStatus from t where rnum = (SELECT Max(rnum) FROM t) 

Or, if you want to select one of the repeating lines, counting only the request identifier, and show the last HistoryStatus, use the query below

 with t as (select row_number()over(partition by RequestID order by RequestID) as rnum,* from tbltmp) Select RequestID,CreatedDate,HistoryStatus from ta where rnum in (SELECT Max(rnum) FROM t GROUP BY RequestID,CreatedDate having t.RequestID=a.RequestID) 

All the above queries written on sql server 2005.

Answer 2

From the title, which I assume, you only need one result for each unique row? If so, take a look at GROUP BY (or SELECT DISTINCT ).

Answer 3

 select t.* from ( select RequestID, max(CreatedDate) as MaxCreatedDate from table1 group by RequestID ) tm inner join table1 t on tm.RequestID = t.RequestID and tm.MaxCreatedDate = t.CreatedDate 

Answer 4

If your query has a one-to-many relationship, duplicate rows can occur on one side.

Assume that <

 TABLE TEAM ID TEAM_NAME 0 BULLS 1 LAKERS TABLE PLAYER ID TEAM_ID PLAYER_NAME 0 0 JORDAN 1 0 PIPPEN 

And you execute a request like

 SELECT TEAM.TEAM_NAME, PLAYER.PLAYER_NAME FROM TEAM INNER JOIN PLAYER 

You'll get

 TEAM_NAME PLAYER_NAME BULLS JORDAN BULLS PIPPEN 

So, you will have a duplicate group name. Even using the DISTINCT clause, your result set will contain a duplicate group name

So, if you do not want to duplicate TEAM_NAME in your request, do the following

 SELECT ID, TEAM_NAME FROM TEAM 

And for each command identifier found

 SELECT PLAYER_NAME FROM PLAYER WHERE TEAM_ID = <PUT_TEAM_ID_RIGHT_HERE> 

This way you will not get duplicate links on one side

Yours faithfully,

Answer 5

If this is a SQL question, and I understand what you are asking (this is not entirely clear), just add a different one from the query

  Select Distinct * From TempTable 

Answer 6

 select * from temptable where rnum --unique key in ( SELECT RNUM --unique key FROM temptable WHERE ( HistoryStatus ) IN (SELECT HistoryStatus FROM temptable GROUP BY HistoryStatus HAVING COUNT(*) <= 1)); 

I have not tested this code. I used similar code and it works. The syntax is in Oracle.

Answer 7

using namespaces and subqueries you can do this:

 declare @data table (RequestID varchar(20), CreatedDate datetime, HistoryStatus varchar(20)) insert into @data values ('CF-0000001','8/26/2009 1:07:01 PM','For Review'); insert into @data values ('CF-0000001','8/26/2009 1:07:01 PM','Completed'); insert into @data values ('CF-0000112','8/26/2009 1:07:01 PM','For Review'); insert into @data values ('CF-0000113','8/26/2009 1:07:01 PM','For Review'); insert into @data values ('CF-0000114','8/26/2009 1:07:01 PM','Completed'); insert into @data values ('CF-0000115','8/26/2009 1:07:01 PM','Completed'); select d1.RequestID,d1.CreatedDate,d1.HistoryStatus from @data d1 where d1.HistoryStatus = 'Completed' union all select d2.RequestID,d2.CreatedDate,d2.HistoryStatus from @data d2 where d2.HistoryStatus = 'For Review' and d2.RequestID not in ( select RequestID from @data where HistoryStatus = 'Completed' and CreatedDate = d2.CreatedDate ) 

Above request returned

 CF-0000001, 2009-08-26 13:07:01.000, Completed CF-0000114, 2009-08-26 13:07:01.000, Completed CF-0000115, 2009-08-26 13:07:01.000, Completed CF-0000112, 2009-08-26 13:07:01.000, For Review CF-0000113, 2009-08-26 13:07:01.000, For Review 

Answer 8

try using highlighted x. * from (your request)

Thanks.

Answer 9

To extract only one single record from a repeating two-row column, you can use the "rowid" column, which is supported by oracle itself as the primary key, so try first

 "select rowid,RequestID,CreatedDate,HistoryStatus from temptable;" 

and then you can get the second row only by the value of the "rowid" column using the SELECT statement.