Prev: Simple model and T-log
Next: Propiedades del SQL Server
From: Qaspec on 20 May 2010 17:09
I want to find the next greater value after min for "NextOrder". I'm
currently using Max but I know there are orders that have a date greater than
the Min but less that the Max....can this be done?

SELECT CNumber, Min (CreateDate) as FirstOrder, Max (CreateDate) as
NextOrder
FROM dbo.tblOrder (nolock)

Where CreateDate Between '1/1/10' and '5/1/10' and CNumber = '0514'

Group By CustomerNumber
From: Erland Sommarskog on 20 May 2010 17:53
Qaspec (Qaspec(a)discussions.microsoft.com) writes:
> I want to find the next greater value after min for "NextOrder". I'm
> currently using Max but I know there are orders that have a date greater
> than the Min but less that the Max....can this be done?
>
> SELECT CNumber, Min (CreateDate) as FirstOrder, Max (CreateDate) as
> NextOrder
> FROM dbo.tblOrder (nolock)
>
> Where CreateDate Between '1/1/10' and '5/1/10' and CNumber = '0514'
> Group By CustomerNumber

I cannot really map your question to your query. It is also confusing to
see MAX(CreateDate) AS NextOrder. I would expect NextOrder to be an
order ID or somesuch - not a date.

If you want to find the second smallest value you can do:

WITH numbered AS (
SELECT partcol, value,
rowno = row_number() OVER(PARTITION BY partcol ORDER BY value)
FROM tbl
)
SELECT partcol, value
FROM numbered
WHERE partcol = 2

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 20 May 2010 18:45
I am not sure I understand, but try the following query:

SELECT CNumber, CreateDate
FROM (
SELECT CNumber, CreateDate, ROW_NUMBER() OVER(ORDER BY CreateDate) AS
rk
FROM dbo.tblOrder
WHERE CreateDate BETWEEN '20100101' AND '20100501'
AND CNumber = '0514') AS T
WHERE rk = 2;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Iain Sharp on 21 May 2010 09:17
On Thu, 20 May 2010 14:09:01 -0700, Qaspec
<Qaspec(a)discussions.microsoft.com> wrote:

>I want to find the next greater value after min for "NextOrder". I'm
>currently using Max but I know there are orders that have a date greater than
>the Min but less that the Max....can this be done?
>
>SELECT CNumber, Min (CreateDate) as FirstOrder, Max (CreateDate) as
>NextOrder
>FROM dbo.tblOrder (nolock)
>
>Where CreateDate Between '1/1/10' and '5/1/10' and CNumber = '0514'
>
>Group By CustomerNumber

Generic (including SQL 2000)


select main.CustomerNumber, firstorder, min(next.createdate) as
nextorder
from (select CustomerNumber,min(createdate) as firstorder
from tblorder (nolock)
group by CustomerNumber) as main
left outer join tblorder as next (nolock) on main.CustomerNumber =
next.CustomerNumber and next.createdate > firstorder
group by main.CustomerNumber, firstorder



SQL 2005 or Up.

with Orders as
(select CustomerNumber, CreateDate , row_number() over (partition by
CustomerNumber order by CreateDate) as rk
from dbo.tblOrder (nolock) )
select firstorder.CustomerNUmber
, firstorder.CreateDate
, secondorder.CreateDate as Nextorder
from orders firstorder
left outer join orders secondorder on secondorder.rk = 2 and
firstorder.CustomerNumber = secondorder.CustomerNumber
where firstorder.rk = 1


this second one gives you all the orders ranked by createdate for a
customer, so you can use rk=3 for the third order and so on and so
forth.


Iain
From: Qaspec on 21 May 2010 10:39
I'm getting the following error -

'ROW_NUMBER' is not a recognized function name.

I am working with Microsoft SQL Server 2005 - Management Studio

"Plamen Ratchev" wrote:

> I am not sure I understand, but try the following query:
>
> SELECT CNumber, CreateDate
> FROM (
> SELECT CNumber, CreateDate, ROW_NUMBER() OVER(ORDER BY CreateDate) AS
> rk
> FROM dbo.tblOrder
> WHERE CreateDate BETWEEN '20100101' AND '20100501'
> AND CNumber = '0514') AS T
> WHERE rk = 2;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Simple model and T-log
Next: Propiedades del SQL Server