How to select max date using SQL without using group by clause . There are solutions for this which have group by clause I want solution without it

Question

I've following table:

PERSON_ID   EFFECTIVE_END_DATE      ASSIGNMENT_ID   FULL_NAME
33151       2013-08-04 00:00:00.0   33885           Test, C
33151       2013-10-04 00:00:00.0   33885           Test, C
33151       2015-02-19 00:00:00.0   33885           Test, C
33151       2013-08-04 00:00:00.0   33885           Test, C
33151       2013-10-04 00:00:00.0   33885           Test, C
33151       2015-02-19 00:00:00.0   33885           Test, C

Here PERSON_ID is same I want to select row with maximum effective end date without using group by.

Can some one help me ?

why don't you want to use the `GROUP BY` clause? This is exactly it's being need for. — VMAtm, Mar 18 '15 at 08:16

score 3 · Accepted Answer · edited May 23 '17 at 12:33

In Oracle you can use:

select * from 
  (SELECT your_column_name 
   FROM table_name 
   order by EFFECTIVE_END_DATE desc) 
where rownum=1;

OR

SELECT *
FROM (
  SELECT your_columns, row_number() over (order by EFFECTIVE_END_DATE desc) EED
      FROM table_name)
WHERE EED = 1

OR

 SELECT *
    FROM table_name as t1
    WHERE EFFECTIVE_END_DATE = (
        SELECT MAX(t2.EFFECTIVE_END_DATE)
        FROM table_name as t2
        WHERE t1.PERSON_ID = t2.PERSON_ID)

Refer This:

MAX(DATE) - SQL ORACLE

This is a similar example

score 2 · Answer 2 · answered Mar 18 '15 at 07:29

You might have duplicate rows too. You could use Analytic ranking function to fetch the required rows.

Set up:

SQL> CREATE TABLE Table1
  2      (PERSON_ID int, EFFECTIVE_END_DATE varchar2(21), ASSIGNMENT_ID int, FULL_NAME varchar2(7));

Table created.

SQL>
SQL> INSERT ALL
  2      INTO Table1 (PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID, FULL_NAME)
  3           VALUES (33151, to_date('2013-08-04','YYYY-MM-DD'), 33885, 'Test, C')
  4      INTO Table1 (PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID, FULL_NAME)
  5           VALUES (33151, to_date('2013-10-04','YYYY-MM-DD'), 33885, 'Test, C')
  6      INTO Table1 (PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID, FULL_NAME)
  7           VALUES (33151, to_date('2015-02-19','YYYY-MM-DD'), 33885, 'Test, C')
  8      INTO Table1 (PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID, FULL_NAME)
  9           VALUES (33151, to_date('2013-08-04','YYYY-MM-DD'), 33885, 'Test, C')
 10      INTO Table1 (PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID, FULL_NAME)
 11           VALUES (33151, to_date('2013-10-04','YYYY-MM-DD'), 33885, 'Test, C')
 12      INTO Table1 (PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID, FULL_NAME)
 13           VALUES (33151, to_date('2015-02-19','YYYY-MM-DD'), 33885, 'Test, C')
 14  SELECT * FROM dual;

6 rows created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM table1;

 PERSON_ID EFFECTIVE_END_DATE    ASSIGNMENT_ID FULL_NA
---------- --------------------- ------------- -------
     33151 04-AUG-13                     33885 Test, C
     33151 04-OCT-13                     33885 Test, C
     33151 19-FEB-15                     33885 Test, C
     33151 04-AUG-13                     33885 Test, C
     33151 04-OCT-13                     33885 Test, C
     33151 19-FEB-15                     33885 Test, C

6 rows selected.

SQL>

Test case:

SQL> WITH data AS
  2    (SELECT t.*,
  3      rank() over(partition BY person_id order by person_id, EFFECTIVE_END_DATE DESC) rn
  4    FROM table1 t
  5    )
  6  SELECT PERSON_ID,
  7    EFFECTIVE_END_DATE,
  8    ASSIGNMENT_ID,
  9    FULL_NAME
 10  FROM data
 11  WHERE rn = 1;

 PERSON_ID EFFECTIVE_END_DATE    ASSIGNMENT_ID FULL_NA
---------- --------------------- ------------- -------
     33151 19-FEB-15                     33885 Test, C
     33151 19-FEB-15                     33885 Test, C

SQL>

score 1 · Answer 3 · answered Mar 18 '15 at 08:25

1

SELECT *
FROM table t
WHERE NOT EXISTS ( SELECT 'a'
                   FROM table t2
                   WHERE t2.PERSON_ID = t.PERSON_ID
                   AND t2.EFFECTIVE_END_DATE > t.EFFECTIVE_END_DATE
                 )

This is the simpliest way

answered Mar 18 '15 at 08:25

Javaluca

859
1
6
13

How to select max date using SQL without using group by clause . There are solutions for this which have group by clause I want solution without it

3 Answers3