Forum Stats

  • 3,768,293 Users
  • 2,252,772 Discussions
  • 7,874,519 Comments

Discussions

SQL Query to get Unique Records from Based on Start Date, Emp Num, Batch

User_EVWYZ
User_EVWYZ Member Posts: 2 Green Ribbon

Hi All,

Need help on the SQL Query

We have a Table


The Query need to fetch unique records based on combination of Batch, Emp No and Max Date

Tried the below query, but it is not fetching the desired results


SELECT

 Table__14.UNIQUE_TRANSACTIONID,

 Table__14.BATCH_NUMBER,

 Table__14.EMP_NUM,

 MAX(to_date(Table__14.START_DATE))

FROM

 DI_CAPTURE Table__14

WHERE

 Table__14.STATUS <> 'REJECTED'

GROUP BY Table__14.UNIQUE_TRANSACTIONID,Table__14.BATCH_NUMBER,Table__14.EMP_NUM,Table__14.START_DATE

Tagged:

Answers

  • JonWat
    JonWat Member Posts: 537 Silver Badge

    You probably just need to remove the last item from your group by.

  • User_EVWYZ
    User_EVWYZ Member Posts: 2 Green Ribbon

    Hi Jon ,

    I tried; it is getting all the records. It is not fetching Unique records.

  • JonWat
    JonWat Member Posts: 537 Silver Badge

    Taking another look at your desired output it looks like you in fact one row per combination of batch number and emp num. In which case your group by should include only those two columns, and in your select you need an aggregate operator on the transaction_ID. maybe min()?

  • mathguy
    mathguy Member Posts: 10,154 Blue Diamond
    edited Oct 15, 2021 2:32PM

    Why do you need to wrap START_DATE within TO_DATE? Is it stored as a string? If it is, can you change that, so it is stored correctly in the date data type? (If it is a date already, then wrapping it within TO_DATE is both unnecessary and dangerous - you may get errors, or wrong results.)

    Then: Don't group by UNIQUE_TRANSACTIONID (obviously: you will get different groups for each transaction id, which is not what you need). Similarly, don't group by START_DATE, that makes no sense.

    But then, you will not be able to select UNIQUE_TRANSACTIONID in the SELECT list. (Which is probably why you added it to GROUP BY in the first place). Instead, use the LAST aggregate function. The query should look like this:

    select max(unique_transactionid) keep (dense_rank last order by start_date) as unique_transactionid,
           batch_number, 
           emp_num, 
           max(start_date) as start_date
    from   di_capture
    where  [... add your filters here ...]
    group  by batch_number, emp_num
    ;
    

    (And, if needed, wrap start_date within TO_DATE as discussed above.)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    edited Oct 15, 2021 4:58PM

    Hi, @User_EVWYZ

    Sorry, it's not clear what you want. Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    The Query need to fetch unique records based on combination of Batch, Emp No and Max Date

    There are lots of different ways results could be based on those columns. Explain exactly how your results are based on them. Start by explaining what each row of the output represents.

    What is a "record"? If it's a row, say "row".