Duplicate rows in Oracle

Question

How can i prevent duplicate rows being selected in a select query?

I have a table with the following fields:

• name
• type
• user1
• user2
• user3
• date

My query requires me to select data for a particular user only at a time which is entered by the user at front end.. Say user enters 1, then the select query should retreive data for user1 only.

I am currently doing it like this:

select name,type,date from table1 order by user1;

But I'm getting redundant rows in the result??

what i am doing wrong? How to avoid these duplicate rows?

Please help...

Answer 1

try SELECT DISTINCT

Answer 2

I don't know if I get your question completely. But I will try anyway:

1. If you want to filter on certain user based on user input try:

   select name, type, "date" from table1 where user = [WHATEVER YOUR USER INPUTS] order by name

2. If you want to pick a different column (user1, user2, user3) depending on user input try:

   select name, type, "date", decode(YOUR_USER_INPUT, 1, user1, 2, user2, 3, user3) from table1 order by name

3. If you just want different results, try select distinct

   select distinct name, type, "date", user1, user2, user3 from table1

Please do provide an example so we can help accordingly. Good luck, gudluck.

Answer 3

You have two options SELECT DISTINCT or use a GROUP BY clause. You also have a date column and in Oracle that also means there is time so to be safe you should truncate the date column. Even if you know time is no part of the insert truncate anyways. It good practice that when you see date you consider it has time.

The next issue you'll encounter is you can not have an ORDER BY on a column that is not part of the SELECT.

My recommendation is a GROUP BY.

SELECT user1, name, type, TRUNC(date) date
FROM Table1
GROUP BY user1, name, type, TRUNC(date)
ORDER BY user1