1

I have a table with data entered manually by the customer, there are some data which is duplicated but just a value in one column is different. For example

+------------+-------+--------+----------+
| Title      | start | end   | Location  |
+------------+-------+--------+----------+
| Title - abc| 10:00 | 10:30  | Loc1     |
| Title - def| 10:00 | 10:30  | Loc1     |
| Title - abc| 11:00 | 11:30  | Loc1     |
| Title - def| 11:00 | 11:30  | Loc1     |
| Title - ghi| 12:00 | 12:30  | Loc1     |
| Title - jkl| 13:00 | 13:30  | Loc1     |
| Title - mno| 14:00 | 14:30  | Loc1     |
+------------+-------+--------+----------+

Output:

+------------+-------+--------+----------+
| Title      | start | end   | Location  |
+------------+-------+--------+----------+
| Title - abc| 10:00 | 10:30  | Loc1     |
| Title - abc| 11:00 | 11:30  | Loc1     |
| Title - ghi| 12:00 | 12:30  | Loc1     |
| Title - jkl| 13:00 | 13:30  | Loc1     |
| Title - mno| 14:00 | 14:30  | Loc1     |
+------------+-------+--------+----------+

I want any one row it can be Title - abc or Title - def Can anyone please help me which this issue.

  • Welcome! What did you try? Why didn't it work? Is the output what you are getting now, or what you expect to get? It's not clear from the wording of your question which doesn't seem to match this output. Which version of Oracle are you using? Please don't answer these questions in a comment but edit your question. When you're done I can delete this comment. – Colin 't Hart Mar 19 at 8:08
  • 1
    Does this answer your question? How to select the first row of each group? – Michael Green Mar 19 at 10:48
1

This is an example of where Analytic functions can shine! In this case, you have a choice between the FIRST_VALUE() function (also its "mirror image" - LAST_VALUE()) and the ROW_NUMBER() function! This thread gave me the idea for using FIRST/LAST_VALUE to emulate PostgreSQL's DISTINCT ON. See the fiddle here!

Create and populate the table:

CREATE TABLE my_tab
(
  title VARCHAR (25) NOT NULL,
  start_time TIMESTAMP NOT NULL,
  end_time   TIMESTAMP NOT NULL,
  location    VARCHAR (10) NOT NULL,
  CONSTRAINT my_tab_pk PRIMARY KEY (title, start_time, end_time, location),
  CONSTRAINT my_tab_st_lt_et_ck CHECK (start_time < end_time)
);

populate (sample):

INSERT INTO my_tab VALUES 
(
  'Title - abc', 
  TO_DATE('2020-03-18 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), 
  TO_DATE('2020-03-18 10:30:00', 'yyyy-mm-dd hh24:mi:ss'), 
  'Loc1'
);

You have to put in a date as well as a time - Oracle doesn't have a TIME datatype - see here. This thread also proved helpful for final formatting of the answer using the TO_CHAR function.

And then (to check), run:

SELECT * FROM my_tab;
ORDER BY location, start_time, end_time, title;

Result:

TITLE           START_TIME                      END_TIME                      LOCATION
Title - abc     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1
Title - def     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1
Title - abc     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1
Title - def     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1
Title - ghi     18-MAR-20 12.00.00.000000 PM    18-MAR-20 12.30.00.000000 PM    Loc1
Title - jkl     18-MAR-20 01.00.00.000000 PM    18-MAR-20 01.30.00.000000 PM    Loc1
Title - mno     18-MAR-20 02.00.00.000000 PM    18-MAR-20 02.30.00.000000 PM    Loc1

We can see that abc and def overlap at 10:00 and 11:00.

FIRST_VALUE()

In the fiddle there is a first attempt which shows my train of thought - not included here.

The query:

SELECT 
  DISTINCT
  FIRST_VALUE (t.title) OVER (PARTITION BY t.start_time ORDER BY t.start_time ASC) AS title,
  TO_CHAR(t.start_time, 'HH24:MI') AS "start",  -- be careful, both "start" and "end" 
  TO_CHAR(t.end_time,   'HH24:MI') AS "end",     -- SQL keywords - fails if not quoted!
  t.location
FROM my_tab t
-- can have ORDER BY 2, 1, but I think actual names are more legible!
-- Again, the SQL keyword "start" must be quoted!
ORDER BY "start", title;

Result:

TITLE           start     end   LOCATION
Title - abc     10:00   10:30       Loc1
Title - abc     11:00   11:30       Loc1
Title - ghi     12:00   12:30       Loc1
Title - jkl     13:00   13:30       Loc1
Title - mno     14:00   14:30       Loc1

Et voilĂ  - the desired result. If you want to eliminate abc instead of removing abc, you can use the LAST_VALUE() function or change the ORDER BY in the OVER() clause of the query (shown in the fiddle - not included here).

ROW_NUMBER()

I'll run the inner query of my final query first so that my train of thought can be easily followed:

SELECT 
  m.title,
  m.start_time,
  m.end_time,
  m.location,
  ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
FROM my_tab m;

Result:

TITLE           START_TIME                      END_TIME                     LOCATION  RN
Title - abc     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1    1
Title - def     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1    2
Title - abc     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1    1
Title - def     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1    2
Title - ghi     18-MAR-20 12.00.00.000000 PM    18-MAR-20 12.30.00.000000 PM    Loc1    1
Title - jkl     18-MAR-20 01.00.00.000000 PM    18-MAR-20 01.30.00.000000 PM    Loc1    1
Title - mno     18-MAR-20 02.00.00.000000 PM    18-MAR-20 02.30.00.000000 PM    Loc1    1

We can see that we want to hold on to the records where rn = 1, so we wrap this in another query (since we can't put Analytic functions in WHERE clauses).

So, we run the following:

SELECT 
  t.title, 
  TO_CHAR(t.start_time, 'HH24:MI') AS "start",  -- be careful, both "start" and end are
  TO_CHAR(t.end_time,   'HH24:MI') AS "end",    -- SQL keywords - fails if not quoted!
  t.location
FROM
(
  SELECT 
    m.title,
    m.start_time,
    m.end_time,
    m.location,
    ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
  FROM my_tab m
) t
WHERE rn = 1
ORDER BY t.title, t.location, t.start_time;

Result:

      TITLE     start     end  LOCATION
Title - abc     10:00   10:30      Loc1
Title - abc     11:00   11:30      Loc1
Title - ghi     12:00   12:30      Loc1
Title - jkl     13:00   13:30      Loc1
Title - mno     14:00   14:30      Loc1

Et voilĂ  encore - the desired result again. If you have a preference for one title over another, you can vary the ORDER BY clause in the OVER part of the ROW_NUMBER() function.

Analytic (aka Window) functions are very powerful and will repay (many, many times over) any effort you put into learning them! As previously mentioned, this thread proved very helpful.

For future reference, henceforth, could you please include your table structure(s) as DDL (CREATE TABLE foo (f1 t1,....);) and your data as DML (INSERT INTO foo VALUES (v1, v2...);)? It's a help to have a single point of truth for the data and saves duplication of effort!

p.s. welcome to the forum!

| improve this answer | |
  • Thank you for your detail explanation, it really helped me in solving the problem. I already had the table and data in the database but I was finding it tough to get the desired result from this table. Anyway your detail explanation has helped me in knowing more about Analytics Functions. – Nik Mar 19 at 13:38
  • Glad you learned something - I learned a lot too! The only way to truly understand something is to explain it to somebody else - +1 for the question! :-) – Vérace Mar 19 at 13:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.