当前位置: 动力学知识库 > 问答 > 编程问答 >

php - removing duplicate rows if one of the columns are the same Sql-server

问题描述:

i have a query which links table one two table two and gets the last comment for the result in table one, which works but when there are multiple records from table one with the same ID for example it shows that result again here is an example

Query results

ID | Machine | description | createdtime | product code | work order | qty | comment | Reson

No

129 |1 | A name | 2015-01-08 07:38:41.427 | A code | 12/14/0038 | 4000 | comment | Reason

143 |1 | A name | 2015-01-08 13:30:39.403 | A code | 12/14/0038 | 4000 | comment | Reson

130 |4 | A name | 2015-01-08 07:38:46.540 | A code | 12/14/0045 | 12000 | comment | Reason

131 |5 | A name | 2015-01-08 07:38:50.243 | A code | 01/15/0001 | 4000 | comment | Reason

As you can see here there are two records with the machine number 1, one created in the morning and one in the after noon, however i only want the latest one to show up, Here is my query.

SELECT Qualitycontrol.ID, Qualitycontrol.MachineNo, Qualitycontrol.Description, Qualitycontrol.CreatedTime, Qualitycontrol.ProductCode, Qualitycontrol.WorkOrder,

Qualitycontrol.Quantity, Qc.Comment, Qc.Reason

FROM Qualitycontrol

OUTER APPLY (

SELECT TOP 1 *

FROM QualityControl_Comments

WHERE Qualitycontrol.ID = QCUID

ORDER BY Qualitycontrol.ID DESC -- whatevet defines order in QualityControl_Comments

) AS Qc

WHERE (Qualitycontrol.CreatedTime BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))

ORDER BY Qualitycontrol.MachineNo

网友答案:

Perhaps something like this would work.

SELECT     Qualitycontrol.ID, Qualitycontrol.MachineNo, Qualitycontrol.Description,        Qualitycontrol.CreatedTime, Qualitycontrol.ProductCode, Qualitycontrol.WorkOrder, 
              Qualitycontrol.Quantity, Qc.Comment, Qc.Reason
FROM         Qualitycontrol 
OUTER APPLY (
  SELECT TOP 1 *
FROM QualityControl_Comments
WHERE Qualitycontrol.ID = QualityControl_Comments.QCUID
ORDER BY Qualitycontrol.ID DESC -- whatevet defines order in QualityControl_Comments
) AS Qc                  
INNER JOIN 
(
    SELECT Qualitycontrol.MachineNo, MAX(Qualitycontrol.ID) MID
    FROM Qualitycontrol
    GROUP BY Qualitycontrol.MachineNo
) UNQ ON UNQ.MID = Qualitycontrol.ID
WHERE     (Qualitycontrol.CreatedTime BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1,     CAST(GETDATE() AS DATE)))
ORDER BY Qualitycontrol.MachineNo
网友答案:

You can use somthing like this. Using row_number you can partition your data by machnine id and sort the numer by date. Than you can just use a simple where clouse to select what you want

;WITH CTE
as
(
SELECT     Qualitycontrol.ID, Qualitycontrol.MachineNo, Qualitycontrol.Description,        Qualitycontrol.CreatedTime, Qualitycontrol.ProductCode, Qualitycontrol.WorkOrder, 
              Qualitycontrol.Quantity, Qc.Comment, Qc.Reason
FROM         Qualitycontrol 
OUTER APPLY (
  SELECT TOP 1 *
FROM QualityControl_Comments
WHERE Qualitycontrol.ID = QCUID

) AS Qc                  
WHERE     (Qualitycontrol.CreatedTime BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1,     CAST(GETDATE() AS DATE)))

), CTE2 as 
(
select *, row_number() over(partition by MachineNo order by CreatedTime desc) as 'row_index' from cte
)
select * from cte2
where row_index = 1
order by MachineNo
分享给朋友:
您可能感兴趣的文章:
随机阅读: