Im having trouble to get the distinct sales from my DB, I tried INNER JOIN but it doesn't work. My SQL statement is something like this:

SELECT * FROM (SELECT DISTINCT(mta.Maintenance.userId)  AS a
FROM mta.Maintenance WHERE 1=1)  AS t 
INNER JOIN mta.Maintenance
ON t.a = mta.Maintenance.userId 
WHERE 1=1
AND mta.Maintenance.paymentStatus = 'PAID' 
AND mta.Maintenance.createdAt < 1648771199000 
AND mta.Maintenance.createdAt > 1640995199000 
AND mta.Maintenance.deletedAt IS NULL 
AND mta.Maintenance.price > 50

And the table is something like this:

And I want:

Basically the distincts results with the MIN(createdAt).

Thanks!!

CodePudding user response：

Try using ROW_NUMBER() to group the rows by UserId and assign a sort number based on the CreatedAt value. Then grab rows where SortNum = 1

WITH cte AS (
   SELECT *, ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY CreatedAt ASC) AS SortNum
   FROM   Maintenance
)
SELECT * 
FROM   cte 
WHERE  RowNum = 1

db<>fiddle here

CodePudding user response：

Try using the MySQL IN Operator of MySQL and from there select the min(createdat) column and group them by a in the main query.

SELECT * FROM maintenance a
WHERE createdat 
IN (SELECT MIN(createdat) FROM maintenance b WHERE b.a=a.a) 
GROUP BY a;

RESULT

     a  maintenanceId  adminId  storeId  vehicleId  serviceId  paymentStatus  STATUS       createdAt  userId  
------  -------------  -------  -------  ---------  ---------  -------------  -------  -------------  --------
    65             50   (NULL)        6         26         38  PAID           PENDING  1644237405347        65
    71             70       28        3         32         32  PAID           PENDING  1644499807732        71
    78             75       52        6         36         38  PAID           PENDING  1644858138158        78