How to get the null and non null records from the table?

Hi,
    I have one table sample_table. It contains the records like below. This is just sample table
ID        PAR_ID      NAME      SURNAME
100       1000          SURI        
200       1000         RAJ           XXX
300      1000       SURIYA    
        The above sample table contains some sort of records. I want to get the records if the surname column is not null i should return that not null row for that particular PAR_ID. If the SURNAME column is null, I should return any one of the row from table for that particular PAR_ID. For example the above table contains the XXX record in SURNAME column. So that column does not contain null value. So i need to return that XXX row from the table for that particular PAR_ID. But if that XXX is not avaiable, all the rows are null for that column, i need to return any of the row from that table for particular PAR_ID.
           For this we have to write SQL query. Can anyone share with me if you get something.

Thanks
Suriyaraj_SudalaiappanAsked:
Who is Participating?
 
awking00Commented:
See attached.
get-records.txt
0
 
MikeOM_DBACommented:

And you want us to do this homework for you?
0
 
Alexey_VarlamovCommented:
The idea to return exactly one row from first or second condition:
Oracle syntax:
  SELECT * FROM
   (SELECT * FROM sample_table WHERE SURNAME IS NOT NULL AND ROWNUM <= 1
     union
     SELECT * FROM sample_table WHERE SURNAME IS NULL AND ROWNUM <= 1
   )  
  WHERE ROWNUM <= 1

MS SQL Syntax:

  SELECT TOP 1 * FROM
   (SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NOT NULL
     union
     SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NULL
   )  
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Alexey_VarlamovCommented:
and add the  " AND PAR_ID = ... " to the first and second subquery...

0
 
Suriyaraj_SudalaiappanAuthor Commented:
               This is not homework for you mike. This is our thoughts you know. I was thinking this since from the morning. But this problem will be really interesting you know.
0
 
MikeOM_DBACommented:

Well, I'm sceptic because I have recently seen similar requests.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
      hI ALEX, Thanks for your reply. We have to use this in where condition. For example

SELECT  SURNAME
FROM SAMPLE_TABLE
WHERE PAR_ID = 100
AND (             )

                 Based on this where condition SURNAME will return value if it contains. If it is not return any value we have to consider as null.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
Good One
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.