How to select unique records from a table

Question

0.00/5 (No votes)

See more:

Hi,
i had a table name uniq_select_records with columns

C#

(sno number, 
item_name varchar2(50),
start_date date, 
end_date date, 
action_flag char(1),
last_modified_date date, 
creation_date date)

sno i a column which i used to identify a row for a item_name and it is not unique or primary key but it was not null. sno will be present for each item. no of items is equal to no of sno. sample

C#

sno   item_name start_date      end_date   Action_flag last_modified creation_date
---  ---------- ---------       ---------- ----------- ------------- -------------
1	sample1	02-MAY-15	27-MAY-14	A	07-MAY-15	02-MAY-15
1	sample1	02-MAY-15	27-MAY-14	D	07-MAY-15	02-MAY-15
1	sample1	27-APR-15	06-JUN-14	C	07-MAY-15	02-MAY-15
1	sample1	27-APR-15	06-JUN-14	C	07-MAY-15	03-MAY-15
2	sample2	07-MAY-15	11-FEB-15	C	07-MAY-15	22-JAN-15
2	sample2	07-MAY-15	11-FEB-15	A	07-MAY-15	22-JAN-15
2	sample2	07-MAY-15	01-FEB-15	C	12-MAY-15	12-MAY-15

C#

Action flag values C=change, D=Deleted and A=insert

sample data is shown above. like this we have millions of records. now we have to select the to record which had the latest change for each item. each item may not have the same start_date and end_date.All the items weren't modified on the same date.
can anyone suggest idea how to fetch the records for each item which are latest change for an item.

expected output is

XML

sno   item_name start_date      end_date   Action_flag last_modified creation_date
---  ---------- ---------       ---------- ----------- ------------- -------------
1   sample1 27-APR-15   06-JUN-14   C   07-MAY-15   03-MAY-15
2   sample2 07-MAY-15   01-FEB-15   C   12-MAY-15   12-MAY-15

thanks in advance....

Posted 21-May-15 23:31pm

Smart003

Updated 8-Jun-15 1:24am

v4

Add a Solution

Comments

Praveen Kumar Upadhyay 22-May-15 7:11am

So you want the unique record based on last_modified?

Smart003 22-May-15 8:48am

yes Praveen i want to list the records based on the last_modified

Mathi Mani 22-May-15 12:46pm

What did you try? Can you share the query you have?

Smart003 25-May-15 7:41am

select * from uniq_select_records where last_modified in (select max(last_modified) from uniq_select_records);

Thava Rajan 25-May-15 7:56am

what is the output you want to show from the query

Thava Rajan 25-May-15 7:57am

need some more sample data

Smart003 1-Jun-15 2:47am

actually i had given sample data for two items

Thava Rajan 25-May-15 7:57am

if possible provide a sqlfiddle in your question

Smart003 29-May-15 9:27am

just use the placed link for sqlfiddle http://www.sqlfiddle.com/#!4/34376/4/0

Thava Rajan 3-Jun-15 23:53pm

what is your expected output

Maciej Los 25-May-15 8:47am

Based on what logic? What result are you expecting to achieve?

Smart003 1-Jun-15 1:40am

only distinct records should be displayed with latest change

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2015-05-25T09:22:00

Solution 1

SQL

WITH last_mod AS (
    SELECT
            sno
           ,Max(last_modified_date) last_modified_date
    FROM    uniq_select_records
    GROUP BY sno
    )
SELECT
        u.sno
       ,u.item_name
       ,u.start_date
       ,u.end_date
       ,u.Action_flag
       ,u.last_modified_date
       ,u.creation_date
FROM    uniq_select_records u
JOIN    last_mod l
    ON  u.sno = l.sno
    AND u.last_modified_date = l.last_modified_date)

Posted 25-May-15 9:22am

Jörgen Andersson

Comments

Maciej Los 25-May-15 17:22pm

Looks promisingly ;) +5!
I'd add one note: Oracle supports CTE starting from 9.2 version ;) Source: WITH Clause : Subquery Factoring

Jörgen Andersson 26-May-15 1:48am

Yeah, I know, but 9i was released in 2002, so I'm frankly expecting people to have moved on.