CODEWITHSUNDEEP
sqlms-access
Matt Hall
Matt Hall

Reputation: 2412

Keep all columns in MIN / MAX query, but return 1 result

I'm sure I've done this before, but seem to have forgotten how..

I'm trying to filter a recordset so that I get just the 1 record, so for example, if this is my table called TableA:

|  ID  |  User |  Type  |   Date   |
------------------------------------
|  1   | Matt  | Opened | 1/8/2014 |
|  2   | Matt  | Opened | 2/8/2014 |
|  3   | Matt  | Created| 5/8/2014 |
|  4   | John  | Opened | 1/8/2014 |
|  5   | John  | Created| 2/8/2014 |

I'd want to filter it so I get the MIN of Date where the User is "Matt" and the Type is "Opened".

The result set needs to include the ID field and return just the 1 record, so it would look like this:

|  ID  |  User |  Type  |   Date   |
------------------------------------
|  1   | Matt  | Opened | 1/8/2014 |

I'm struggling with getting past the GROUPBY requirement when selecting the ID field... this seems to ignore MIN of Date and return more than 1 record.

Upvotes: 1

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Use TOP and ORDER BY:

select top 1 *
from table
where user = "Matt" and type = "Opened"
order by date asc;

Edit: changed order by from desc to asc as this achieves the MIN effect I'm after.

Upvotes: 3

akhil vangala
akhil vangala

Reputation: 1053

you can try using partition functions very easy its gives result for each user and performs better 

;WITH cte
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY [USER]
            ,type ORDER BY DATE ASC
            ) rnk
    FROM tablea
    )
SELECT *
FROM cte
WHERE type = 'opened'
    AND rnk = 1

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Another way is by finding the min or max date per user and type then join the result back to the main table

SELECT A.ID,
       A.USER,
       A.Type,
       A.Date
FROM   yourtable A
       INNER JOIN (SELECT USER,
                          Type,
                          Min(Date) Date
                   FROM   yourtable
                   WHERE  USER = "Matt"
                          AND type = "Opened"
                   GROUP  BY USER,
                             Type) B
               ON A.USER = B.USER
                  AND A.Type = B.Type
                  AND A.date = B.Date

Upvotes: 1

Related Questions