Return null values if there is no record for top n selected

Question

I am trying to get the top ‘n’ rows from the following query:
SELECT TOP 1 ImageName FROM (SELECT TOP 5 * FROM ClientKeywordMedia where keywordid=1147 ORDER BY ID) z ORDER BY ID DESC
The above query is working fine if top 5 is used in subquery as there is only 5 records in subquery.
The problem occurred when I am trying to run the following query
SELECT TOP 1 ImageName FROM (SELECT TOP 7 * FROM ClientKeywordMedia where keywordid=1147 ORDER BY ID) z ORDER BY ID DESC
Its returning the 5th rows values as 7th row, which is incorrect, because there are only 5 rows for the subquery.
Any ideas how to return false or null record if there are only 5 records if we run top 7

Answer 1

Try the next query too:

select ImageName from ( select *, ROW_NUMBER() over (order by ID) as rn from ClientKeywordMedia where keywordid=1147) as t where t.rn = 7

It does not return any record if the row does not exist.

Answer 2

;WITH CTE AS (
    SELECT TOP 7 ID, ImageName
    FROM ClientKeywordMedia 
    where keywordid=1147 
    ORDER BY ID
)

SELECT TOP 1 CASE WHEN (SELECT COUNT(*) FROM CTE) = 7 THEN ImageName ELSE NULL END AS ImageName 
FROM CTE
ORDER BY ID DESC;

Answer 3

Hi sudhirbharti，

Please find another method from below:

select top 1 ImageName FROM (
    SELECT TOP 7 isDummy,ID,keywordid,ImageName FROM (
        SELECT 1 isDummy,* FROM ClientKeywordMedia where keywordid=1147 
        UNION ALL 
        SELECT 0 isDummy,t1.* FROM ClientKeywordMedia T1
              RIGHT JOIN INFORMATION_SCHEMA.COLUMNS ON t1.id = -1000
    )A ORDER BY isDummy desc
) B  ORDER BY isDummy ,ID DESC 

/*
ImageName
NULL
*/

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa