Select Top n Where n > recordcount

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Top n Where n > recordcount

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-16 : 07:53:36

Charlie writes "The query below returns the date of the 3rd deposit due in a recordset, but if there are only 2 deposits due this query returns the second deposit due as the third. How can I force a null or blank on the third deposit if ther isn't one.

SELECT top 1 recordid, convert(varchar,deposit_due_on,107) AS DueDate
FROM (SELECT TOP 100 recordid,deposit_due_on FROM (SELECT TOP 3 recordid,deposit_due_on FROM TABLE_NAME
WHERE recordid=2)a order by a.deposit_due_on desc ) b

regards

Charlie"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 08:57:28

Try this..

SELECT top 1 recordid, convert(varchar,deposit_due_on,107) AS DueDate
FROM 
(
    SELECT TOP 100 recordid,deposit_due_on 
    FROM 
    (
        SELECT TOP 3 recordid, deposit_due_on FROM TABLE_NAME WHERE recordid = 2 union all
        SELECT TOP 1 NULL as recordid, NULL as deposit_due_on union all
        SELECT TOP 1 NULL as recordid, NULL as deposit_due_on union all
        SELECT TOP 1 NULL as recordid, NULL as deposit_due_on
    )a 
    order by a.deposit_due_on desc 
) b

----------------------------------
'KH'
Time is always against us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources