CODEWITHSUNDEEP
sqlsql-server
Ashan
Ashan

Reputation: 479

getting same top 1 result in sql server

I have this query:

SELECT 
    IT_approvaldate 
FROM  
    t_item  
WHERE 
    IT_certID_fk_ind = (SELECT DISTINCT TOP 1 IT_certID_fk_ind 
                        FROM t_item  
                        WHERE IT_rfileID_fk = '4876') 
ORDER BY 
    IT_typesort

Result when running this query:

enter image description here

I need get top 1 result. (2013-04-27 00:00:00) problem is when I select top 1, getting 2nd result.

enter image description here

I believe reason for that order by column value same in those two result. please see below,

enter image description here

However I need get only IT_approvaldate column top 1 as result of my query.

How can I do this? Can anyone help me to solve this?

enter image description here

Upvotes: 1

Views: 1142

Answers (3)

clifton_h
clifton_h

Reputation: 1298

TSQL Select queries are not inherently deterministic. You must add a tie-breaker or by another row that is not.

The theory is SQL Server will not presume that the NULL value is greater or lesser than your row, and because your select statement is not logically implemented until after your HAVING clause, the order depends on how the database is setup.

Understand that SQL Server may not necessarily choose the same path twice unless it thinks it is absolutely better. This is the reason for the ORDER BY clause, which will treat NULLs consistently (assuming there is a unique grouping).

UPDATE: It seemed a good idea to add a link to MSDN's documentation on the ORDER BY. Truly, it is good practice to start from the Standard/MSDN. ORDER BY Clause - MSDN

Upvotes: 1

Swapnil
Swapnil

Reputation: 434

If you want NULL to be the last value in the sorted list you can use ISNULL in ORDER BY clause to replace NULL by MAX value of DATETIME Below code might help:

SELECT  TOP 1 IT_approvaldate
FROM  t_item 
WHERE IT_certID_fk_ind = (SELECT DISTINCT top 1 IT_certID_fk_ind FROM  t_item  WHERE IT_rfileID_fk ='4876' ) 
ORDER BY IT_typesort ASC, ISNULL(IT_approvaldate,'12-31-9999 23:59:59') ASC;

Upvotes: 1

Dotnetpickles
Dotnetpickles

Reputation: 1026

Hi use below query and check

SELECT IT_approvaldate FROM  t_item  WHERE IT_certID_fk_ind =(SELECT DISTINCT top 1 IT_certID_fk_ind FROM  t_item  WHERE IT_rfileID_fk ='4876' ) and IT_approvaldate is not null ORDER BY IT_typesort

This will remove null values from the result

Upvotes: 1

Related Questions