CODEWITHSUNDEEP
sqlms-access-2007
user793468
user793468

Reputation: 4966

query more than two columns with distinct in one column

I have a table with four columns and want to display distinct output based on one columns only. How can I do this?

I tried using Max function on ID but it only works for two columns, anything more than two columns displays all records.

Any help is much appreciated

--EDIT--

Sample Data:

ID Name Date    Status
1  Sam  1/1/11    A
2  Sam  2/22/12   A
3  Jill 1/5/10    R
4  Jim  7/2/12    I
5  Jim  5/6/11    I
6  Bob  11/11/12  A
7  Bob  7/26/10   A

Output:

ID Name Date    Status
1  Sam  1/1/11    A
3  Jill 1/5/10    R
4  Jim  7/2/12    I
6  Bob  11/11/12  A

Upvotes: 2

Views: 731

Answers (3)

monika
monika

Reputation: 499

Not sure if this is what you want but try:

SELECT DISTINCT columnYouFilterBy, column2, column3, column4 FROM yourTable

Upvotes: 0

HansUp
HansUp

Reputation: 97101

It seems you want values from the row with the lowest ID value for each Name. Use a GROUP BY subquery to get the minimum ID per Name. Then INNER JOIN your table to the subquery.

SELECT y2.ID, y2.Name, y2.Date, y2.Status
FROM
    YourTable AS y2
    INNER JOIN
        (
            SELECT y.Name, Min(y.ID) AS MinOfID
            FROM YourTable AS y
            GROUP BY y.Name
        ) AS sub
    ON y2.ID = sub.MinOfID

Upvotes: 2

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

Not sure what you want to return from your other columns, but something like this would return the unique values in col1, and the minimum values from the other columns.

SELECT 
    Col1,
    MIN(Col2) AS Col2,
    MIN(Col3) AS Col3,
    MIN(Col4) AS Col4
FROM MyTable
GROUP BY Col1

Upvotes: 0

Related Questions