Untitled

mail@pastecode.io avatar
unknown
mysql
a year ago
982 B
1
Indexable
Never
SELECT 
    COUNT(DISTINCT serialnumber) AS units,
    COUNT(DISTINCT bin) AS bins,
    username,
    MAX(lastscandate) AS lastscandate,
    (SELECT 
            location
        FROM
            shard.inventoryitemupdate
        WHERE
            username = t1.username
                AND lastscandate >= TIMESTAMPADD(SECOND, 1, CURRENT_DATE)
        ORDER BY lastscandate DESC
        LIMIT 1) AS location
FROM
    shard.inventoryitemupdate t1
WHERE
    lastscandate >= TIMESTAMPADD(SECOND, 1, CURRENT_DATE)
        AND username IN ('Joshua Holdren' , 'Johana Aracely Rodriguez',
        'Jenny Yip',
        'Elena Zavala Diaz',
        'Nancy Marie Moreno',
        'Christian Anthony Rivas',
        'Beatriz Rivas',
        'Flor Del Carmen Rivas',
        'Jonathan Ryu',
        'Coty Phanniphon',
        'Laura Ayala',
        'Josefina Quezada',
        'Ruben Esparza',
        'Salvador Benjaran Ortega')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC