CODEWITHSUNDEEP
sqloracle-database
Damien-Amen
Damien-Amen

Reputation: 7492

Distinct values in my table

say I have a table with following column and records

NAME            RESULT   DATE
----------------------------------------------
John            Pass    12-FEB-2013 18:03:55
Albert          Fail    12-FEB-2013 18:24:29
Chris           Pass    12-FEB-2013 18:24:28
John            Fail    12-FEB-2013 19:32:35
Chris           Pass    12-FEB-2013 19:32:35
Steve           Pass    12-FEB-2013 20:04:35

now if I need distinct values from NAME column I did the following

SELECT DISTINCT(NAME) FROM TABLE_NAME;

now If I do the following

SELECT DISTINCT(NAME), 
       RESULT, 
       DATE 
FROM TABLE_NAME 
WHERE TRUNC(DATE) = TRUNC(SYSDATE)-4;

I get all the RECORDS in the names column. But I don't need all the records. I only need distinct names even with the above WHERE clause. Pls help me.

Upvotes: 0

Views: 174

Answers (2)

user330315
user330315

Reputation:

DISTINCT is NOT a function. You simply added paranthesises to the column name. It's essentially the same as the difference between select name, result ... and select (name), result ...

It is an operator that works on the complete row. And because of that you get all rows because the combination of name, result, date is always different (btw. date is a horrible name for a column. Not only because it's a reserved word, but also because it doesn't document what's in the column. Is it a birthdate? A termination date? A start date? An end date? ...).

If you only want e.g the latest date for each name, you need a group by:

select name, 
       max(result) as result,
       max(trunc(date)) as dt
from table_name 
group by name;

Note that this does not necessarily return the result that belongs to the max date. If you need that, consider something like this:

select name, result, result_date
from (
    select name, 
           result,
           trunc(date) as result_date,
           row_number() over (partition by name order by date desc) as rn
    from table_name 
) t
where rn = 1;
group by name;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Your question is a little confusing. If you want to retrieve the DISTINCT names using your supplied WHERE criteria, just do this:

SELECT DISTINCT NAME 
FROM TABLE_NAME 
WHERE TRUNC(DATE) = TRUNC(SYSDATE)-4;

If however you want the other fields with the date truncated:

SELECT DISTINCT NAME, RESULT, TRUNC(DATE) dt
FROM TABLE_NAME 
WHERE TRUNC(DATE) = TRUNC(SYSDATE)-4;

Or, if you want the full time:

SELECT DISTINCT NAME, RESULT, DATE
FROM TABLE_NAME 
WHERE TRUNC(DATE) = TRUNC(SYSDATE)-4;

Hope this helps.

Upvotes: 0

Related Questions