CODEWITHSUNDEEP
sqloracle-database
Hawk
Hawk

Reputation: 5170

Show the oldest or newest records without showing the date

I have the following tables:

account

+------------+--------------+
| account_id | account_name |
+------------+--------------+
|        452 | a            |
|        785 | b            |
|        985 | c            |
+------------+--------------+

and

task 

+---------+------------+------------+
| task_id | task_date  | account_id |
+---------+------------+------------+
|       2 | 01-01-2013 |        452 |
|       1 | 14-02-2013 |        452 |
|       5 | 03-01-2013 |        452 |
|       1 | 02-02-2013 |        785 |
|       7 | 07-01-2013 |        785 |
|       5 | 01-03-2013 |        785 |
|       1 | 25-03-2013 |        985 |
|       4 | 22-03-2013 |        985 |
+---------+------------+------------+

I need to show records from the table task and account such that only the oldest task can be shown and without showing the date. So the result will be: 

+--------------+---------+
| account_name | task_id |
+--------------+---------+
| a            |       2 |
| b            |       7 |
| c            |       4 |
+--------------+---------+

Using min function came to my mind. But since I do not want to show the date, how can I use the function. 

SELECT A.ACCOUNT_NAME, 
       T.TASK_ID, 
       MIN(T.TASK_DATE)  
  FROM ACCOUNT A  
 INNER JOIN TASK T  
    ON A.ACCOUNT_ID = T.ACCOUNT_ID  
 GROUP BY ACCOUNT_NAME, TASK_ID

Upvotes: 1

Views: 111

Answers (6)

R R
R R

Reputation: 2956

You can use this simple approach:

SELECT a.account_name, t.task_id
FROM   account a,
       task t
WHERE  a.account_id= t.account_id 
AND    t.task_date in (SELECT MIN(t.task_date))
GROUP BY account_name, task_id;

Upvotes: 1

Gerardo Lima
Gerardo Lima

Reputation: 6713

It's easier than it may seem: after joining tables normally, just filter by the max(tak_date), that you can calculate on a simple sub-select.

SELECT acc.account_name, tsk.task_id
FROM account acc
JOIN task    tsk on (acc.account_id = tsk.account_id)
WHERE tsk.task_date = (
  SELECT MAX(aux.task_date)
  FROM task aux
  WHERE aux.account_id = tsk.account_id 
)

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

Another approach, a bit shorter:

select max(a.accountname) as account_name
     , max(t.taskid) keep(dense_rank first
                               order by t.taskdate) as task_id
 from account1 a
 join task t
   on (a.accountid = t.accountid)
group by a.accountid

result:

ACCOUNT_NAME    TASK_ID
------------ ----------
a                     2
b                     7
c                     4

SQLFiddle Demo

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

You can either use analytics:

SELECT account_name, task_id
  FROM (SELECT a.account_name,
               t.task_id,
               row_number() 
                  over(PARTITION BY a.account_id ORDER BY t.task_date) task_num
          FROM account a
         INNER JOIN task t ON a.account_id = t.account_id
         GROUP BY account_name, task_id)
 WHERE task_num = 1

or a self-join:

SELECT a.account_name, t.task_id
  FROM account a
 INNER JOIN task t ON a.account_id = t.account_id
 WHERE (t.account_id, t.task_date) = (SELECT t_in.account_id, 
                                             MIN(t_in.task_date) 
                                         FROM task t_in 
                                        GROUP BY t_in.account_id)

Upvotes: 1

Arion
Arion

Reputation: 31249

Maybe something like this:

WITH CTE
AS
(
    SELECT 
        A.ACCOUNT_NAME, 
        T.TASK_ID, 
        ROW_NUMBER() OVER(PARTITION BY T.TASK_ID 
                     ORDER BY T.TASK_DATE DESC) AS RowNbr
    FROM ACCOUNT A
    INNER JOIN TASK T
    ON A.ACCOUNT_ID = T.ACCOUNT_ID
)
SELECT
    CTE.ACCOUNT_NAME,
    CTE.TASK_ID
FROM
    CTE
WHERE 
    CTE.RowNbr=1;

Upvotes: 1

Emyl
Emyl

Reputation: 10536

You can simply use a subquery:

SELECT S.ACCOUNT_NAME, S.TASK_ID FROM (
  SELECT A.ACCOUNT_NAME, T.TASK_ID, MIN(T.TASK_DATE)
  FROM ACCOUNT A
  INNER JOIN TASK T
  ON A.ACCOUNT_ID = T.ACCOUNT_ID
  GROUP BY ACCOUNT_NAME, TASK_ID
) S

Upvotes: 1

Related Questions