CODEWITHSUNDEEP
oracle-database
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

ORACLE: How to get all column with GROUP by only 1 column?

I'm using ORACLE Database, How to get all column with GROUP by only 1 column (EMP_ID)?

Example I have table ESD_RESULTS

FIRST_NAME | LAST_NAME | EMP_ID | WRIST_STATUS | LFOOT_STATUS | DATE
Dodo       | A         | 0101   | Pass         | Pass         | 2016-01-18 10:00
Wedi       | Wil       | 0105   | Pass         | Pass         | 2016-01-18 10:05
Dodo       | A         | 0101   | Pass         | Fail         | 2016-01-18 10:11

What I want the data display is (Get the last data by date desc if EMP_ID same):

FIRST_NAME | LAST_NAME | EMP_ID | WRIST_STATUS | LFOOT_STATUS | DATE
Dodo       | A         | 0101   | Pass         | Fail         | 2016-01-18 10:11
Wedi       | Wil       | 0105   | Pass         | Pass         | 2016-01-18 10:05

I tried to use DISTINCT and GROUP by the data still show all.

Upvotes: 1

Views: 4080

Answers (2)

user5683823
user5683823

Reputation:

Since presumably the first name and the last name are determined by the emp_id (they don't change from one row to another), you might as well group by all three columns - resulting in less work. (On the other hand, it would make more sense to normalize your table design; one table shows the associated first name and last name for each emp_id, there is no need to repeat the first name and last name in "this" table, which you show in your post.)

Then: you can use the FIRST/LAST function, with keep (dense_rank ...), as demonstrated below, to eliminate the need for a subquery and an outer query. If there is the possibility of two rows having the exact same date and time for an emp_id, you may refine the query to accommodate "tie-breaks" of some kind. If there are no ties, then the query will work without modification.

DATE is a reserved word in Oracle, it shouldn't be used for table or column names. I changed it to DT.

with
     test_data ( first_name, last_name, emp_id, wrist_status, lfoot_status, dt ) as (
       select 'Dodo', 'A'  , 0101, 'Pass', 'Pass', to_date('2016-01-18 10:00', 'yyyy-mm-dd hh24:mi') from dual union all
       select 'Wedi', 'Wil', 0105, 'Pass', 'Pass', to_date('2016-01-18 10:05', 'yyyy-mm-dd hh24:mi') from dual union all
       select 'Dodo', 'A'  , 0101, 'Pass', 'Fail', to_date('2016-01-18 10:11', 'yyyy-mm-dd hh24:mi') from dual
     )
-- end of test data (NOT part of the solution); SQL query begins BELOW THIS LINE
select   first_name, last_name, emp_id,
         min(wrist_status) keep (dense_rank last order by dt) as wrist_status,
         min(lfoot_status) keep (dense_rank last order by dt) as lfoot_status,
         max(dt) as dt
from     test_data
group by first_name, last_name, emp_id
;

FIRST_NAME LAST_NAME     EMP_ID WRIST_STATUS LFOOT_STATUS DT
---------- --------- ---------- ------------ ------------ ----------------
Dodo       A                101 Pass         Fail         2016-01-18 10:11
Wedi       Wil              105 Pass         Pass         2016-01-18 10:05

2 rows selected.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

One option is to use ROW_NUMBER() to identify the latest record for each employee:

SELECT t.FIRST_NAME,
       t.LAST_NAME,
       t.EMP_ID,
       t.WRIST_STATUS,
       t.LFOOT_STATUS,
       t.DATE
FROM
(
    SELECT FIRST_NAME, LAST_NAME, EMP_ID, WRIST_STATUS, LFOOT_STATUS, DATE,
           ROW_NUMBER() OVER (PARTITION BY EMP_ID ORDER BY DATE DESC) rn
    FROM ESD_RESULTS
) t
WHERE t.rn = 1

Upvotes: 5

Related Questions