MySQL Query to list all distinct applications

[zanb]

Hello! I'm looking to build a distinct list of applications from Macs within a certain building. I've modified a sample query to give me a list of all applications on all computers, but I don't want a report of computer names with all applications installed on them. I want a distinct list of applications.

SELECT DISTINCT a.computer_name AS 'Computer Name', c.application_name AS 'Application Name', c.application_version AS 'Version', c.application_path AS 'Application Path'
From computers_denormalized a INNER JOIN reports b ON a.computer_id = b.computer_id AND computer_name LIKE 'Building1%'
INNER JOIN applications c ON b.report_id = c.report_id
ORDER BY a.computer_name

Thanks! I appreciate the help.

[zanb · Accepted solution]

Hey again,

With a little bit of searching and RTFM-ing, I was able to learn about subqueries and ordering by the application name within the subquery to generate my report. Here's what I came up with:

SELECT DISTINCT r.g_app, r.g_vers, r.g_path FROM 
(
    SELECT DISTINCT a.computer_name AS 'g_name', 
                    c.application_name AS 'g_app', 
                    c.application_version AS 'g_vers', 
                                    c.application_path AS 'g_path'
    From computers_denormalized a  INNER JOIN reports b ON a.computer_id = b.computer_id AND computer_name LIKE 'Building1%'
    INNER JOIN applications c ON b.report_id = c.report_id
    ORDER BY c.application_name
) r