Prev: Audit trigger
Next: Need Assistance with a SQL query
From: Joachim Hofmann on 8 Apr 2010 12:17
Hello,

I have the following rowset:


i dt1 dt2
----------- ----------------------- -----------------------
1 2010-03-30 00:00:00.000 NULL
2 2010-03-31 00:00:00.000 NULL
3 2010-04-01 00:00:00.000 NULL
4 2010-04-02 00:00:00.000 2010-04-02 00:00:00.000
5 2010-04-03 00:00:00.000 2010-04-03 00:00:00.000
6 2010-04-04 00:00:00.000 2010-04-04 00:00:00.000
7 2010-04-05 00:00:00.000 2010-04-05 00:00:00.000
8 2010-04-06 00:00:00.000 NULL
9 2010-04-07 00:00:00.000 NULL
10 2010-04-08 00:00:00.000 NULL

Now i want to query the n-th dt1 where dt2 is not null.

This would be the line for n = 5
9 2010-04-07 00:00:00.000 NULL

How can I query this?


Thank You

Joachim
From: Plamen Ratchev on 8 Apr 2010 12:52
Did you mean the n-th row where dt2 is NULL? Because this is what the desired result looks like.

Try this:

SELECT dt1, dt2
FROM (
SELECT dt1, dt2, ROW_NUMBER() OVER(ORDER BY dt1) AS rk
FROM Foo
WHERE dt2 IS NULL) AS F
WHERE rk = 5;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Joachim Hofmann on 9 Apr 2010 12:54
Plamen Ratchev schrieb:
> Did you mean the n-th row where dt2 is NULL? Because this is what the
> desired result looks like.
>
> Try this:
>
> SELECT dt1, dt2
> FROM (
> SELECT dt1, dt2, ROW_NUMBER() OVER(ORDER BY dt1) AS rk
> FROM Foo
> WHERE dt2 IS NULL) AS F
> WHERE rk = 5;
>


Thank You, this was the first time I used ROW_NUMBER() .

Joachim
 | 
Pages: 1
Prev: Audit trigger
Next: Need Assistance with a SQL query