MySQL statement to skip duplicates

Question

how could I skip duplicate AssetId and get latest UtcTimeStamp for each AssetId?

I have this statement written and can not figure it out so far.

SELECT MobileAsset.[AssetId]
,MobileAsset.[Name] 
,Asset.[AState]
,AssetHistory.[UtcTimeStamp]
FROM [MobileAsset]
inner join Asset on Asset.AssetId = MobileAsset.AssetId 
inner join AssetHistory on AssetHistory.AssetId = Asset.AssetId
where AState != 'scrapped' AND attribute = 'State' and (MobileAsset.AssetId is not null) and OldValue = 'Prestaged' and NewValue = 'In stock'
Order By UtcTimeStamp DESC

I get this result:

AssetId Name        AState  UtcTimeStamp
3000030 M3000030            2020-01-29 12:11:15.703
3000030 M3000030    In use  2020-01-29 12:11:10.147
3000030 M3000030    In use  2020-01-29 12:09:16.873
3000024 M3000024    In use  2020-01-29 08:38:56.470

I want to skip multiple Asset ID: 3000030

score 0 · Answer 1 · answered Feb 27 '20 at 14:43

0

Use DISTINCT keword.

SELECT DISTINCT MobileAsset.[AssetId].....

Append the DISTINCT keyword for all fields that you need to have distinct values.

answered Feb 27 '20 at 14:43

Nishant Agarwal

430
5
17

Hello! Thank You for the input, but it seems that it does not work either. – Tautvis Feb 27 '20 at 14:49
Maybe this will help : https://stackoverflow.com/questions/11277251/selecting-distinct-2-columns-combination-in-mysql – Nishant Agarwal Feb 27 '20 at 15:00

score 0 · Answer 2 · answered Feb 27 '20 at 14:44

To simply collect just one of each row you can use something like this

SELECT
    *
FROM
    `test`
GROUP BY
    `AssetId`
ORDER BY
    UtcTimeStamp
DESC

This will get you this Result

3000030     M3000030                    2020-01-29
3000024     M3000024        In use  2020-01-29

MySQL statement to skip duplicates

2 Answers2