1

I want to select all the columns but the column name given in DISTINCT is repeated twice.
For Intance,

SELECT DISTICT emp_name,* FROM EMPLOYEE

In above case, column emp_name is getting selected twice. How can I avoid it?

UPDATE:
How to apply DISTINCT on selected column while returning multiple columns? FOR instance: 

SELECT DISTINCT(emp_name), emp_address, doj FROM EMPLOYEE

In above example DISTINCT must be applied on emp_name.

Prasad Jadhav
  • 5,090
  • 16
  • 62
  • 80
  • This should answer your question, it is a previous answer on stack overflow. Good luck. [Select Distinct, but all columns][1] [1]: http://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns – Landjea Oct 19 '12 at 14:11
  • 4
    You should NEVER use select * in the first place. It is a SQL antipattern. List your columns. – HLGEM Oct 19 '12 at 14:12
  • If you ask for `distinct` on all columns you will get all rows without duplicates. Is that what you want? Or you want just names? If the later you can't include other columns in the `distinct` list. If the former then use only `*` or the column names as commented above. – Clodoaldo Neto Oct 19 '12 at 14:34
  • @HLGEM There is a case where the use of `*` is the only right solution. It is when I just want to get all rows without duplication. In this case if the columns names are listed and after that the column set grows (a column is added) the query becomes wrong. – Clodoaldo Neto Oct 19 '12 at 14:40
  • You probably want `GROUP BY`, but your requirement is close to ill-formed. If you have multiple rows with the same `emp_name` but different values for the other columns, what values do you want in the result for those other columns? – zinglon Oct 19 '12 at 16:32
  • duplicate entry for `emp_name` indicates that same employee has edited and its previous value is preserved too. So I am taking only that row which has latest "created on" value. – Prasad Jadhav Oct 19 '12 at 17:02
  • @Clodoaldo, that is a very bad use of select *. What happens when someone adds an auditing column or somwething you don't want the user to see? Letting the columns come in willy nilly in whatever order they were aded makes the screen get badly designed. ANd while I do not believe anyone shoudl rearrange teh order of teh columns in the table when they add columns. people do and tehn that can make your reports or screens do the wrong thing. It causes performance issues, you often return more than you need especially when you have join where you return the join columns multiple times. – HLGEM Oct 19 '12 at 17:32

2 Answers2

3

Remove emp_name

SELECT DISTINCT * FROM EMPLOYEE

or replace * with all columns:

SELECT DISTINCT emp_name, col1, col2 ... FROM EMPLOYEE

you can also use:

SELECT max(emp_name), emp_address, doj 
FROM EMPLOYEE
GROUP BY emp_address,doj
Robert
  • 25,425
  • 8
  • 67
  • 81
1

You could just not include it twice in your query:

SELECT DISTINCT * FROM EMPLOYEE
RB.
  • 36,301
  • 12
  • 91
  • 131
  • 2
    @PrasadJadhav What do you mean `Distinct *` "is not working for me"? Do you receive an error? Does it not give expected results? Please be as precise as possible as your question is very unclear... – RB. Oct 19 '12 at 14:27
  • it is returning all the rows including duplicates. It doesnt flag any error though. – Prasad Jadhav Oct 19 '12 at 14:29
  • If you use `SELECT DISTINCT` it will not return duplicates (i.e. no 2 rows will contain exactly the same values in all columns). Please can you edit your question to include your input, your output, your **expected** output, and an explanation of why the 2 do not match? – RB. Oct 19 '12 at 14:37
  • inside `DISTINCT *` there is an `id`(primary key) column too. So real question remains: How can I apply `DISTINCT` on single column while returning multiple columns? – Prasad Jadhav Oct 19 '12 at 15:48