Counting 1st Record only

[J. Joshi]

Hello,

I am trying to write a query which will identify only
those members who have used either one or more services in
a given year. 1 MemberID can have 1 or more UnitID's. This
means that if I group the report by UnitID's, I will see
multiple MemberID's. I just want to see 1 MemberID based
on utilization (that being >=1) based on first use in a
given calander year.

However, that member should be repeated the next year if
he/she has used the service 1 or more times. I am just
trying to count my members using services irrespective of
the number of times they have used it.

Here is a data-set I am CURRENTLY generating:

Yr Mth MemberID MemType UnitID Type
==============================================
2003 April 7044 AS C Renew
2003 April 7044 AS L Change
2003 June 7044 AS C Change
2003 June 7044 AS L Renew
2004 May 7044 AS C Change
2003 April 5000 NA CL Current
2003 April 5000 NA WSR New
2003 June 5000 NA WSR Change
2004 May 5000 NA CL Change
2004 May 5000 NA WSR Renew


Here is the results set I want to see:

Yr Mth MemberID MemType UnitID Type
==============================================
2003 April 7044 AS C Renew
2004 May 7044 AS C Change
2003 April 5000 NA CL Current
2004 May 5000 NA WSR Renew

Any idea on how would I acieve this result set. I am on
SQL Query Analyzer ver.8.00.194

Thanks so much.

Joshi

[Anith Sen]

Here is a generalized t-SQL method for such requirements,

SELECT *
FROM tbl
WHERE keycol = (SELECT TOP 1 keycol
FROM tbl t1
WHERE t1.dupcol = tbl.dupcol
ORDER BY t1.keycol DESC)

Here the keycol is the column/set of columns which can uniquely identify a
row within the group & dupcol is the column which is duplicated across the
rows.

--
- Anith
( Please reply to newsgroups only )