CODEWITHSUNDEEP
ms-access
Ryan
Ryan

Reputation: 791

How to do a distinct query that returns multiple columns

I need to create a MS Access query that will return only one instance of a serial number along with a few other columns. For example

SerialNumber    CRM        EmailClient   VersionBuild  IPAddress  DateLogged
------------    ----       -----------   ------------  ---------  ----------
123456          Outlook    Outlook       4.7.4         0.0.0.0    20120625
123456          SugarCRM   SMTP          4.6.1         0.0.0.0    20120621
435352          ACT!       Outlook       4.5.2         0.0.0.0    20120619
537352          ACT!       SMTP          4.5.3         0.0.0.0    20120619

The results would be

SerialNumber    CRM        EmailClient   VersionBuild  IPAddress  DateLogged
------------    ----       -----------   ------------  ---------  ----------
123456          Outlook    Outlook       4.7.4         0.0.0.0    20120625
435352          ACT!       Outlook       4.5.2         0.0.0.0    20120619
537352          ACT!       SMTP          4.5.3         0.0.0.0    20120619

I was trying to complete this using the following query. I think First and Group by cancel each other out.

SELECT FIRST(LogData.SerialNumber), LogData.CRM, LogData.EmailClient, LogData.VersionBuild, LogData.IPAddress, LogData.DateLogged
FROM LogData
GROUP BY  LogData.CRM, LogData.EmailClient, LogData.VersionBuild, LogData.IPAddress, LogData.DateLogged

Upvotes: 0

Views: 1366

Answers (3)

HansUp
HansUp

Reputation: 97101

In a comment you mentioned LogData does not have a primary key. If you add an autonumber primary key, your problem becomes very easy to solve.

Use a subquery to return a single id value for each SerialNumber group, and INNER JOIN the subquery to LogData.

SELECT base.*
FROM
    LogData AS base
    INNER JOIN (
        SELECT SerialNumber, Min(id) AS which_id
        FROM LogData
        GROUP BY SerialNumber
    ) AS sub
    ON base.id = sub.which_id;

Replace base.* with a list of the fields you want to display.

Upvotes: 1

Daniel
Daniel

Reputation: 13122

This ain't pretty but...

SELECT L.SerialNumber, 
            (Select Top 1 A.First from LogData as A where A.SerialNumber = L.SerialNumber Order by A.First, A.Last) AS [First], 
            (Select Top 1 A.Last from LogData as A where A.SerialNumber = L.SerialNumber Order by A.First, A.Last) AS [Last]
FROM LogData AS L
GROUP BY L.SerialNumber;

Which is basically Thomas' answer, except that since you are using a subquery you can dictate the order by to make sure you always get the expected results.

Upvotes: 0

Thomas
Thomas

Reputation: 2445

I think you want to do this:

  • get only one SerialNumber => GROUP BY SerialNumber
  • get only one (the first) name for each SerialNumber => FIRST(LogData.First)
SELECT LogData.SerialNumber, FIRST(LogData.First), FIRST(LogData.Last)
FROM LogData
GROUP BY  LogData.SerialNumber

But be be warned: this could result (not in the case of the "FIRST"-function) a mixed record like:

Serial    First    Last
------    -----    ----
123456    Frank    Smith

By the way: The "FIRST"-Grouping-Function is not supported by all SQL-Systems: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html You may use another function to fulfill the sql-standard...

Upvotes: 0

Related Questions