I have a table that has the following layout:

rowid, customerid, worknote, entrydate

Each customerid can have multiple worknotes.  What I need
to do is get the 2 most current worknotes  for each
customer and put them into 2 separate columns in a view
(e.g., customerid, worknote1, worknote2).  Some customerid
may only have a single worknote.

I can build a correlated subquery to get the 2 most current
results per customerid but I can't figure out how to then
display them into multiple columns without cursoring, using
temp tables, etc.

This is the query that gets me the 2 most current records:

select * from CustomerWorknotes t1 where rowid in (select
top 2 t2.rowid from CustomerWorknotes

t2 where t2.customerid = t1.customerid order by entrydate
desc)

Any help would be appreciated.

Thanks.

-John

Assuming one customer does not have 2 worknotes with the same entrydate:

select customer_id,
(select TOP 1 worknote from CustomerWorknotes where customer_id
=cw.customer_id order by entrydate) AS worknote1,
(select TOP 1 worknote from CustomerWorknotes where customer_id
=cw.customer_id
AND entrydate < (SELECT MAX(cwc.entrydate) from CustomerWorknotes cwc where
cwc.customer_id = cw.customer_id)
order by entrydate) AS worknote2
FROM CustomerWorknotes cw

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

For such problems, please post your table DDLs , sample data  & expected
results. Here is a guess:

SELECT *
  FROM tbl
 WHERE keycol IN (SELECT TOP 2 t1.keycol
                    FROM tbl t1
                   WHERE t1.customerid = tbl.customerid
                   ORDER BY t1.entrydate DESC)

--
- Anith
(Please respond only to newsgroups)