Home » SQL & PL/SQL » SQL & PL/SQL » Distinct output
Distinct output [message #253424] Mon, 23 July 2007 17:24 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I need to get distinct output from Oracle 9i database.
If I run this SQL:
select distinct myfieldname from tableOne where myfieldname is not null

I dont get distinct output and instead get this output:

Efg
ABC
Enb
Abc
EFG
Eee

It seems the distinct keyword doesnt filter out upper and lower case results.

If I run this SQL:
select distinct Upper(myfieldname) from tableOne where Upper(myfieldname) is not null

It does give me distinct output but it shows everything as Upper case:
EFG
ABC
ENB
EEE

Please advise how I can get this output:
Efg
Abc
Enb
Eee


Re: Distinct output [message #253425 is a reply to message #253424] Mon, 23 July 2007 17:47 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
florida wrote on Mon, 23 July 2007 18:24

I dont get distinct output and instead get this output:

Efg
ABC
Enb
Abc
EFG
Eee

It seems the distinct keyword doesnt filter out upper and lower case results.


That is distinct output.
florida wrote on Mon, 23 July 2007 18:24


Please advise how I can get this output:
Efg
Abc
Enb
Eee


Why Abc and not ABC and why Efg and not EFG?
Re: Distinct output [message #253426 is a reply to message #253424] Mon, 23 July 2007 18:35 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Using INITCAP function.
Re: Distinct output [message #253633 is a reply to message #253426] Tue, 24 July 2007 08:04 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
flyboy wrote on Mon, 23 July 2007 19:35
Using INITCAP function.


That won't do. It's just another manipulation like UPPER.
Re: Distinct output [message #253636 is a reply to message #253424] Tue, 24 July 2007 08:13 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Florida,
please answer the question. Why is the string "Abc" ok to return, but the string "ABC" isn't valid? Because on a computer system, a data string of "Abc" is not equivilent to "ABC". What exactly do you want?
Re: Distinct output [message #253787 is a reply to message #253636] Tue, 24 July 2007 16:46 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Quote:

Florida,
please answer the question. Why is the string "Abc" ok to return, but the string "ABC" isn't valid? Because on a computer system, a data string of "Abc" is not equivilent to "ABC". What exactly do you want?



Thanks for all the attention on this question.

The values I am fetching I am putting in a Select options area for searching something using JSP front end.

So my selection list for searching will look like this:
Efg
ABC
Enb
Abc
EFG
Eee

ABC and Abc both return the same result in my search and that is why I only wanted one out of the two to show up. From what I am reading on this post, I will have to settle for InitCap or the Upper method.
Re: Distinct output [message #253824 is a reply to message #253424] Wed, 25 July 2007 00:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
To the example in your first post INITCAP is satisfying (cannot see any notice that output values shall be the case-sensitive input ones there).

You can use analytics to get unique case-insensitive values (not tested):
SELECT myfieldname
FROM (
  SELECT myfieldname,
    ROW_NUMBER() OVER (PARTITION BY upper(myfieldname) ORDER BY myfieldname) rn
  FROM tableOne
)
WHERE rn = 1;
Re: Distinct output [message #254311 is a reply to message #253424] Thu, 26 July 2007 07:15 Go to previous messageGo to next message
nivas007
Messages: 7
Registered: July 2007
Location: Hydera bad
Junior Member

select initcap(myfieldname) from tableOne
Re: Distinct output [message #254321 is a reply to message #254311] Thu, 26 July 2007 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't this the first answer from flyboy.
In addition he gave the link on the function.

Please, read the whole topic before posting and post only if you have something new to give.
Please, if you post code then read and follow How to format your posts

Regards
Michel
Re: Distinct output [message #254322 is a reply to message #254311] Thu, 26 July 2007 07:38 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
nivas007 wrote on Thu, 26 July 2007 08:15
select initcap(myfieldname) from tableOne


What question are you answering? Surely not this one. Please read the question first before answering. The other question you answered was an equally perplexing response.
Re: Distinct output [message #254705 is a reply to message #253424] Fri, 27 July 2007 10:41 Go to previous message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,


select distinct(initcap(Col_name)) from tabl_name;


Reds
Thangam.
Previous Topic: is it possible to write "ORDER BY QUARTERLY"
Next Topic: Removing underscores
Goto Forum:
  


Current Time: Thu Dec 08 08:35:02 CST 2016

Total time taken to generate the page: 0.07480 seconds