CODEWITHSUNDEEP
sqlmaxmin
CompilerSaysNo
CompilerSaysNo

Reputation: 415

Finding min and max dates SQL

I would like to create a query where I find the details only for members with the earliest and latest registration_date. So far I have

SELECT member_id, UPPER(name), registration_date 
from db.member WHERE registration_date >=
'2012-01-01'

I am unsure how to find just min/max but am just testing it with the set date 2012-01-01

So the query should only bring back two results - min/max

Thank you in advance

Upvotes: 0

Views: 1526

Answers (3)

gmiley
gmiley

Reputation: 6604

Something like the following, while a little sub-query-heavy, might give you closest to what you need. Depending on the flavor of your SQL (Oracle, SQLServer, MySQL, etc...) you can limit the subqueries for the member_id selects inside of the IN result set subqueries.

select member_id, Upper(name) AS name, registration_date 
from db.member 
where member_id in
(
    select Min(member_id) 
    from db.member
    where registration_date =
    (
        select Min(registration_date)
        from db.member
    )
    union all
    select Max(member_id) 
    from db.member
    where registration_date = 
    (
        select Max(registration_date)
        from db.member
    )
)
order by registration_date desc;

Upvotes: 0

Minesh
Minesh

Reputation: 166

select min(dateColumn),max(dateColumn) from tbl;

Upvotes: 0

radar
radar

Reputation: 13425

you can get min , max dates in a subquery and then can get the results.

select member_id, upper(name), registration_date
from db.member
cross join (
       select min(registration_date) as minDate, max(registration_date) as maxDate
       from db.member ) t
where registration_date in ( t.minDate, t.maxDate)

or you can do it with in and union

select member_id, upper(name), registration_date
from db.member
where registration_date in  (
       select min(registration_date) 
       from db.member
       union 
       select max(registration_date) as maxDate
       from db.member )

Upvotes: 1

Related Questions