Simple Select Question

[pvong]

Newbie needing some help.

Table1 looks like this:

PshipID Period Date
1 100 11/30/2009
2 65 11/30/2009
1 99 10/31/2009
2 64 10/31/2009
1 98 09/30/2009
2 63 09/30/2009

All I want is to get the MAX period (minus) 1 and it's corresponding date.

Example

PshipID Period Date
1 99 10/31/2009
2 64 10/31/2009

I know how to do MAX Period -1 to get the value but I don't know how to lock
the Date to the new Period.

Thanks in advance.

[Bob Barrows]

What if period 64 did not exist (records do get deleted ... )? Would you
want the date associated with period 63? If so, here's one way to do it:

create a saved query called MaxPeriodPerShipID with this sql:
select pshipid,Max(Period) as MaxPeriod
from table1
group by pshipid

Then create a second saved query called PenultimatePeriodPerShipID with
this sql:

select t1.pshipid,Max(t1.Period) as PenultimatePeriod
from table1 as t1 inner join MaxPeriodPerShipID as m
on t1.pshipID = m.pshipid
where t1.Period < MaxPeriod
group by t1.pshipid

Then create a third query with this sql:
SELECT t.pshipid, t.period, t.date
FROM Table3 AS t INNER JOIN PenultimatePeriodPerShipID AS p
ON t.period = p.PenultimatePeriod AND t.pshipid = p.pshipid

--
HTH,
Bob Barrows

[Bob Barrows]

Oops, I answered this from an Access point of view. For SQL Server, read
"view" where I wrote "saved query" ... or use derived tables
(subqueries) instead, like this:

SELECT t.pshipid, t.period, t.date
FROM Table3 AS t INNER JOIN

(select t1.pshipid,Max(t1.Period) as PenultimatePeriod

from table1 as t1 inner join

(select pshipid,Max(Period) as MaxPeriod

from table1
group by pshipid

)

as m
on t1.pshipID = m.pshipid
where t1.Period < MaxPeriod
group by t1.pshipid

)

[pvong]

That's a lot of steps. I thought MAX period (minus) 1 will take care of any
deletion? I'll give your method a try but it's very confusing.

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OC7osLLi...@TK2MSFTNGP04.phx.gbl...

[Plamen Ratchev]

Here is one method:

SELECT pshipid, period, [date]
FROM (
SELECT pshipid, period, [date],
ROW_NUMBER() OVER(PARTITION BY pshipid ORDER BY period DESC) AS rk
FROM PeriodData) AS P
WHERE rk = 2;

--
Plamen Ratchev
http://www.SQLStudio.com

[Bob Barrows]

Will it? What if your data consisted of this:

1 100 11/30/2009
2 65 11/30/2009
1 99 10/31/2009

2 63 10/31/2009
1 98 09/30/2009
2 62 09/30/2009
:
What would you wish the result to be? This?

PshipID Period Date
1 99 10/31/2009

***no record contains PshipID 2 and Period 64

or this?

PshipID Period Date
1 99 10/31/2009

2 63 10/31/2009

If the first result is what you want, then change my suggestion to:

SELECT t.pshipid, t.period, t.date
FROM Table1 AS t INNER JOIN
(select pshipid,Max(Period) as MaxPeriod

from table1
group by pshipid

)
as m
ON t.period = MaxPeriod-1 AND t.pshipid = p.pshipid

--
HTH,
Bob Barrows

[pvong]

This didn't work for me so I broke it down to see why. This is the actual
query for your FROM Select.

SELECT PshipID, Period, EndDate, ROW_NUMBER() OVER(PARTITION BY PshipID
ORDER BY Period DESC) AS rk
FROM Period

This is the error I'm getting

Msg 195, Level 15, State 10, Line 1

'ROW_NUMBER' is not a recognized function name.

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:M4OdnVPmQcx81afW...@speakeasy.net...

[Bob Barrows]

When you don't specify which version of SQL Server you are using, you
run the risk of receiving answers that do not apply to the version you
are using ...

Always preface your requests for help by specifying the version you are
using ... it is almost always relevant.

--
HTH,
Bob Barrows

[pvong]

Sorry about that. I'm doing this in SQL 2005

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:OthCDnLi...@TK2MSFTNGP04.phx.gbl...

[Plamen Ratchev]

pvong wrote:
> Sorry about that. I'm doing this in SQL 2005
>

The ranking functions are supported in SQL Server 2005. The code will work even if you database is in compatibility mode
80 (SQL Server 2000). Most likely you are connected to SQL Server 2000 instance and there the ranking functions do not
work.

[pvong]

Sorry, this stupid DB is actually in SQL 2000 and I have to leave it in
2000 because the 3rd party vendor will not support it if I move it to SQL
2005. Is there a way to do this in SQL2000?

"pvong" <vonger@*dot*com> wrote in message
news:OEiUW5Li...@TK2MSFTNGP05.phx.gbl...

[Bob Barrows]

Then ROW_NUMBER() should work. There are examples of it in SQL 2005
Books Online. I've just successfully tested Plamen's suggestion with
this code:

select 1 pshipid,100 period,cast('20091130' as datetime) date
into #tmp
union all
select 2,65,cast('20091130' as datetime)
union all
select 1,99,cast('20091031' as datetime)
union all
select 2,64,cast('20091031' as datetime)
union all
select 1,98,cast('20091030' as datetime)
union all
select 2,63,cast('20091030' as datetime)

SELECT pshipid, period, [date]
FROM (
SELECT pshipid, period, [date],
ROW_NUMBER() OVER(PARTITION BY pshipid ORDER BY period DESC) AS
rk

FROM #tmp) AS P
WHERE rk = 2;

What do you see when you run:
select @@version

--
HTH,
Bob Barrows

[Bob Barrows]

Yes, my earlier reply will work in SQL 2000. Again, which suggestion you
use depends on which result you want if there are gaps in the periods.

--
HTH,
Bob Barrows

[Plamen Ratchev]

Here is equivalent solution for SQL Server 2000 but have in mind this will be slow on a large result set:

SELECT pshipid, period, [date]
FROM (
SELECT pshipid, period, [date],

(SELECT COUNT(*)
FROM PeriodData AS B
WHERE B.pshipid = A.pshipid
AND B.period <= A.period) AS rk
FROM PeriodData AS A) AS P

[Bob Barrows]

Here is the code I tested in QA against a SQL 2000 server. Note that
there are two queries. Use the one that gives you the result you want:

select 1 pshipid,100 period,cast('20091130' as datetime) date
into #tmp
union all
select 2,65,cast('20091130' as datetime)
union all
select 1,99,cast('20091031' as datetime)
union all

select 2,63,cast('20091031' as datetime)

union all
select 1,98,cast('20091030' as datetime)
union all

select 2,62,cast('20091030' as datetime)

SELECT t.pshipid, t.period, t.date
FROM #tmp AS t INNER JOIN
(select t1.pshipid,Max(t1.Period) as PenultimatePeriod
from #tmp as t1 inner join
(select pshipid,Max(Period) as MaxPeriod
from #tmp

group by pshipid
)
as m

on t1.pshipID = m.pshipid
where t1.Period < MaxPeriod
group by t1.pshipid

)

AS p
ON t.period = p.PenultimatePeriod AND t.pshipid = p.pshipid

SELECT t.pshipid, t.period, t.date
FROM #tmp AS t INNER JOIN
(select pshipid,Max(Period) as MaxPeriod
from #tmp

group by pshipid
)
as m

ON t.period = MaxPeriod-1 AND t.pshipid = m.pshipid

drop table #tmp

--
HTH,
Bob Barrows

[pvong]

This is almost perfect but it's giving me the SECOND period and I am looking
for the SECOND TO THE LAST period.

Thanks!

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message

news:M4OdnU3mQcyw-KfW...@speakeasy.net...

[TheSQLGuru]

Put Plamen's query (without the WHERE clause) into a temp table. Now you
have all data.

now do a select from that table but get each row where rk is (max - 1) for
each set of data:

select pshipid, period, date
from #tmp t1
where rk = (select max(t2.rk) - 1 from #tmp t2 where t2.pshipid = t1.pshipid
and t2.period = t1.period and t2.date = t1.date)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"pvong" <vonger@*dot*com> wrote in message

news:efCBphMi...@TK2MSFTNGP06.phx.gbl...

[Bob Barrows]

Change the B.period <= A.period to B.period >= A.period

--
HTH,
Bob Barrows

[Plamen Ratchev]

Just reverse the ranking calculation:

[pvong]

This worked perfectly!!! Thanks!

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message

news:M4OdnUzmQcyBE6fW...@speakeasy.net...