How Can I Get The Null Valued Records From A Table?

In my table 1,00,000 records are there, where 1000 rows and 100 columns are there, some of the records in the table have NULL values, what i want is If the Null valued recorded of

Solution 1:

select Staff_ID, First_Name, Last_Name
from YourTable
orderbycast(Staff_ID asvarchar(10))+First_Name+Last_Name,
         Staff_ID

NULL values will be ordered first.

When concatenating a value with NULL the result is NULL so First_Name+Last_Name will be NULL if any one is NULL.

cast(Staff_ID as varchar(10))+... is there guarantee the order you want for the case when First_Name+Last_Name is notNULL (I assume Staff_ID is int).

And the extra order by column Staff_ID is there to guarantee the order you want for all the rows where First_Name+Last_NameisNULL

Update

You can build the query dynamically using INFORMATION_SCHEMA.COLUMNS. This might do what you want. Note, this is not tested for all kinds of data types.

declare@TableName sysname  ='YourTable'declare@Sql nvarchar(max) ='select * from '+quotename(@TableName)+' order by 'select@Sql=@Sql+'+cast('+COLUMN_NAME+' as varchar(max))'from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME =@TableNameexec (@Sql)

Update 2

A non dynamic version using XML instead.

;with C as 
( 
  select *,
         row_number() over(orderby (select1)) as rn
  from YourTable
)
select C1.*
from C as C1
  cross apply (select *
               from C as C2
               where C1.rn = C2.rn
               for xml path('x'), type) as T(N)orderby T.N.value('count(/x/*)', 'int')

Update 3

If you know the primary key of your table and don't mind typing the where clause you can use this instead of using row_number().

select Y1.*
from YourTable as Y1
order by (select *
          from YourTable as Y2
          where Y1.Staff_ID = Y2.Staff_ID
          for xml path('x'), type).value('count(/x/*)', 'int')

Solution 2:

select Staff_ID, First_Name, Last_Name FROM [tablename] would get you the output you described ;P nothing in particular needed to print out the null values.

If you want to look for specific null values use WHERE columnname IS NULL

Edit: if you are just looking to sort by null first just do a ORDER BY Last_Name, First_Name ASC/DESC depending on what way you want to sort it.

Solution 3:

SELECT Staff_ID, First_Name, Last_Name
FROM myTable
ORDERBY Last_Name DESC

Also try this:

SELECT Staff_ID, First_Name, Last_Name
FROM myTable
ORDERBY (CASEWHEN Last_Name ISNULLTHEN0ELSE1END), Last_Name DESC

Solution 4:

select Staff_ID , First_Name , Last_Name from Staff where (First_Name IsNullor  

Last_Name isNull)

Solution 5:

'Union'is the answer. 

(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name isnullAND Last_Name isnull)
Union
(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name isnotnullAND Last_Name isnull)
union
(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name isnullAND Last_Name isnotnull)
union
(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name isnotnullAND Last_Name isnotnull)

Query with order by will be challenging so Here it is:

Select Staff_ID, First_Name, Last_Name FROM
(
    (SELECT'1'as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name isnullAND Last_Name isnull)
    Union
    (SELECT'2'as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name isnotnullAND Last_Name isnull)
    union
    (SELECT'3'as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name isnullAND Last_Name isnotnull)
    union
    (SELECT'4'as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name isnotnullAND Last_Name isnotnull)
) as tbl1 orderby tbl1.firstSortPref,  tbl1.First_Name, tbl1.Last_Name