How to SELECT DISTINCT one column and ORDER by another column?-CodePudding

I have a table like this

                                                        
id |  domain  | domain_date           | domain_status  |
1  |abc_com   | 2022-08-20 15:42:35   |       1        |
2  |def_com   | 2022-08-20 15:44:31   |       1        |
3  |def_com   | 2022-08-20 15:40:05   |       1        |
4  |abc_com   | 2022-08-20 15:35:05   |       0        |
5  |ghi_com   | 2022-08-20 15:25:05   |       1        |
6  |ghi_com   | 2022-08-20 15:25:00   |       1        |
7  |abc_com   | 2022-08-19 15:22:00   |       1        |
8  |ghi_com   | 2022-08-20 15:25:05   |       1        |
9  |abc_com   | 2022-08-20 15:21:00   |       1        |
10 |abc_com   | 2022-08-19 15:21:20   |       1        |

I want to get latest entries of five records from the table but I don't want duplicate domains. I tried with SELECT DISTINCT, Distinct works fine if I select only domain it cuts the duplicates but if selects two columns

SELECT DISTINCT domain, domain_date FROM Domains

It still shows duplicates because the dates are different

What is the workaround for this?

CodePudding user response：

if you group on max domain date, you will get a distinct list of domains with their max domain_date:

select 
    domain, max(domain_date) as MaxOfDomainDate
from table
group by domain

We can join the table on this result set if we want to get the status that goes with the domain record for that date:

select A.domain, A.domain_date, A.domain_status
from
table A
inner join
(
    select 
        domain, max(domain_date) as MaxOfDomainDate
    from table
    group by domain
) B
on A.domain = B.domain
and A.domain_date = B.domain_date;

You should be able to add ordering and TOP or LIMIT to such a query pretty easily to get the "latest 5" and so on (I'll leave that as an exercise to the reader ;) )

CodePudding user response：

You may need to create a Join table in which you set both condition for column domain and domain_date.