First of each group

[Bob Stearns]

I could swear we just lately had a discussion on this but I can't find it.

I have a (simplified) table: subj_id, date_checked, status and I want to
retrieve the latest status for each subj_id within a given range of
dates. The primary key is subj_id, date_checked.
select * from t as t2
where t2.subj_id=:subject
and date_checked=(
select max(date_checked)
from t
where subj_id=t2.subj_id
and date_ckecked between :start_date and :end_date)

would do the trick for any given :subject, but I want to wrap it in a
query of the form:

select * from t
where subj_id in (:list)

I'm missing something obvious, but it is eluding me.

[Serge Rielau]

Bob Stearns wrote:

I could swear we just lately had a discussion on this but I can't find it.

I have a (simplified) table: subj_id, date_checked, status and I want to
retrieve the latest status for each subj_id within a given range of
dates. The primary key is subj_id, date_checked.
select * from t as t2
where t2.subj_id=:subject
and date_checked=(
select max(date_checked)
from t
where subj_id=t2.subj_id
and date_ckecked between :start_date and :end_date)

would do the trick for any given :subject, but I want to wrap it in a
query of the form:

select * from t
where subj_id in (:list)

I'm missing something obvious, but it is eluding me.

You are messing ROW_NUMBER() OVER(ORDER BY date_checked DESC PARTITION
BY subj_id). Then you can filter out all but the first.
Depending on your indexing you may still want to do a self join to pull
up extraneous columns after filtering.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

I could swear we just lately had a discussion on this but I can't find
it.

I have a (simplified) table: subj_id, date_checked, status and I want
to retrieve the latest status for each subj_id within a given range of
dates. The primary key is subj_id, date_checked.
select * from t as t2
where t2.subj_id=:subject
and date_checked=(
select max(date_checked)
from t
where subj_id=t2.subj_id
and date_ckecked between :start_date and :end_date)

would do the trick for any given :subject, but I want to wrap it in a
query of the form:

select * from t
where subj_id in (:list)

I'm missing something obvious, but it is eluding me.

You are messing ROW_NUMBER() OVER(ORDER BY date_checked DESC PARTITION
BY subj_id). Then you can filter out all but the first.
Depending on your indexing you may still want to do a self join to pull
up extraneous columns after filtering.

Cheers
Serge

That appears to have the right functionality. The OVER phrases must be
in the order PARTITION BY then ORDER BY according to the SQL Reference
Vol. 1.

I have a complex ORDER BY that is failing with:

SQL0104N An unexpected token "DESC" was found following "END)".
Expected tokens may include: "". SQLSTATE=42601

It is:
ROW_NUMBER() OVER(
PARTITION BY (t1.bhid)
ORDER BY ((CASE t1.bred_type
when 'I' then t1.BRED_DATE + 275 days
else t1.BRED_DATE + 283 days
END) DESC)
) AS rowx

It seem legal according to the syntax diagrams (I have the same CASE
defining a column earlier). Any ideas?

[Tonkuma]

Remove parentheses surrounding (CASE ... DESC):
ROW_NUMBER() OVER(
PARTITION BY (t1.bhid)
ORDER BY (CASE t1.bred_type
when 'I' then t1.BRED_DATE + 275 days

else t1.BRED_DATE + 283 days
END) DESC
) AS rowx

Note: Though they are not syntax error, another two parentheses are not
required.
(t1.bhid)
(CASE .... END)

[Bob Stearns]

Tonkuma wrote:

Remove parentheses surrounding (CASE ... DESC):
ROW_NUMBER() OVER(
PARTITION BY (t1.bhid)
ORDER BY (CASE t1.bred_type
when 'I' then t1.BRED_DATE + 275 days

else t1.BRED_DATE + 283 days
END) DESC
) AS rowx

Note: Though they are not syntax error, another two parentheses are not
required.
(t1.bhid)
(CASE .... END)

Exactly the same error:

An unexpected token "DESC" was found following "END". Expected tokens
may include: "". SQLSTATE=42601

[Tonkuma]

It worked on my DB2 UDB V8.2

------------------------------ Commands Entered
------------------------------
connect to SAMPLE user db2admin using ********;
----------------------------------------------------------------------

Database Connection Information

Database server = DB2/NT 8.2.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE

------------------------- Commands Entered --------------------------
SELECT id, bhid, bred_type, BRED_DATE,
ROW_NUMBER() OVER(
PARTITION BY (t1.bhid)
ORDER BY (CASE t1.bred_type
when 'I' then t1.BRED_DATE + 275 days
else t1.BRED_DATE + 283 days
END) DESC
) AS rowx
FROM (VALUES (1, 1, 'I', CURRENT DATE)
,(2, 1, 'A', CURRENT DATE)
,(3, 1, 'I', CURRENT DATE)
) t1 (id, bhid, bred_type, BRED_DATE);
--------------------------------------------------------------------

ID BHID BRED_TYPE BRED_DATE ROWX
----------- ----------- --------- ---------- --------------------
2 1 A 2005-10-23 1
1 1 I 2005-10-23 2
3 1 I 2005-10-23 3

3 record(s) selected.