CODEWITHSUNDEEP
sqlite
Mdomin45
Mdomin45

Reputation: 469

Finding the oldest customers in a sql database

I'm trying to find the oldest person in a SQL database that has the following configuration:

Customers (
    cardNo INTEGER PRIMARY KEY,
    first TEXT,
    last TEXT,
    sex CHAR,
    dob DATE
)

I'm trying to find the oldest customers in the database, of which there are 28 (they have the same dob). I'm not sure how to get multiple results from the min() keyword.

Upvotes: 0

Views: 421

Answers (2)

Linnaire
Linnaire

Reputation: 61

I believe that MIN() / MAX() is an aggregate function which means that it returns a single scalar value.

More info for the aggregate functions can be found here: Aggregate functions info

But to solve your problem, The query should be like this.

MS SQL

SELECT
 c.first,
 c.last
FROM Customers c
WHERE c.dob IS NOT NULL
AND c.dob = (
    SELECT TOP 1 cc.dob 
    FROM Customers cc 
    WHERE cc.dob IS NOT NULL 
    ORDER BY cc.dob
)
GROUP BY c.dob
ORDER BY c.dob

SQL LITE

SELECT
 c.first,
 c.last
FROM Customers c
WHERE c.dob IS NOT NULL
AND c.dob = (
    SELECT cc.dob 
    FROM Customers cc 
    WHERE cc.dob IS NOT NULL
    ORDER BY cc.dob
    LIMIT 1
)
GROUP BY c.dob
ORDER BY c.dob

I think it will still need optimization. Hope this helps. :)

Upvotes: 0

Korthrun
Korthrun

Reputation: 151

You can do this with a subquery.

Something like:

SELECT first, last FROM Customers
WHERE
    dob = (SELECT MIN(dob) FROM Customers);

Upvotes: 3

Related Questions