Home » SQL & PL/SQL » SQL & PL/SQL » display query as per requirement
display query as per requirement [message #442139] Thu, 04 February 2010 23:52 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
Hi,

I have table
id   fname     lname    midval   name             codeval
 1    abc       xyz       c      xyz,abc c       ab@ccc.com
 2    Abc       Xyz       C      Xyz,Abc C     ab@ccc.com
 3    bcd       pqr               pqr,bcd
 4    bcd       pqr               pqr,bcd      c@ttt.com


Now I want the output

id   fname     lname    midval   name             codeval
 1    abc       xyz       c      xyz,abc c       ab@ccc.com
 4    bcd       pqr               pqr,bcd      cc@ttt.com



I have mentioned few columns above,but I have few more columns

In the above output, I should not display duplicate records and
record which do not have codeval which is the 3rd record.
I want to display as it is in the database,I don't want to change the casesensitivity of the columns and display as it it.

little urgent


thanks,
steve

[Updated on: Fri, 05 February 2010 00:01] by Moderator

Report message to a moderator

Re: display query as per requirement [message #442140 is a reply to message #442139] Fri, 05 February 2010 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63813
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1 and 2 are not duplicates, so why there is 1 and not 2.

If you want a fast answer you should post complete requirements and a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel

[Updated on: Fri, 05 February 2010 00:03]

Report message to a moderator

Re: display query as per requirement [message #442141 is a reply to message #442140] Fri, 05 February 2010 00:06 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
sorry,
forgot to mention it.
I mean fname,lname,midval are same.
so I do not want to display them, though they are case sensitive.
Re: display query as per requirement [message #442146 is a reply to message #442141] Fri, 05 February 2010 00:34 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

Here is solution for your question,


select * from table_name 
where rowid in (select max(rowid) from table_name group by upper(fname),upper(lname),upper(midval))
                  UNION
               (select rowid from table_name where midval is null))


Hope it will help u at least to some extent.

[Updated on: Fri, 05 February 2010 00:37]

Report message to a moderator

Re: display query as per requirement [message #442172 is a reply to message #442141] Fri, 05 February 2010 01:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
stevefaulk wrote on Fri, 05 February 2010 07:06
sorry,
forgot to mention it.
I mean fname,lname,midval are same.
so I do not want to display them, though they are case sensitive.

Again, those columns contain different values for records 1 and 2. If you are not interested in case, what does this requirement mean: "I want to display as it is in the database,I don't want to change the casesensitivity of the columns and display as it it." ?
And which of the two should be selected, and why?
Previous Topic: inserting a row into a table
Next Topic: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g
Goto Forum:
  


Current Time: Fri Sep 30 12:19:30 CDT 2016

Total time taken to generate the page: 0.08082 seconds