eliminate duplicate results

Hi,

Here is a brief description of a problem I'm currently experiencing:

We have a phone line that employees can call to report their status. There is no limit on how often they can call. Each time a call is completed a record is inserted into a table with the following info: the employees unique id, theire current status, and the location of an audio file should they have chosen to leave a message. When I query this table I'd like to only return the most recent call for each employee, disregarding all the rest.

So...

When I do a simple query like:

SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId

I get only one record, the most recent one, for each employee who has called. However as soon as I try to get another field I get one record for each different value in that field.

a second query:

SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId,
r.ircStatus
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId, r.ircStatus

This returns one record for each status value that each employee has.

I guess my question is how do I retrieve all of the fields in a record with the most recent date for each unique employee.

Thanks for the help.Read this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31147

And change MIN to MAX|||Thanks for the quick reply.

Which post in that thread am I looking at?|||Just got it working. With a little tweaking of the second last post in the linked thread everything is perfect

Thanks Brett!

Edition Question for Analysis Services

Hi All

I come with Oracle background and we are planning to use SQL Server 2000 Analysis Services for OLAP.

Brief background on our app: The largest of our fact tables has about 500,000 rows per month. We plan to store three years worth of data. We will have other smaller cubes that we will have to build in addition to this. The source data will come from Oracle tables.

I am struggling to decide between the two SQL Server editions (Standard and Enterprise). I'm highlighting the things that I need some help with in order to make the decision:

Memory Limitation
------
Standard Edition only supports up to 2GB. Considering relatively large cube size (is it?), is this a limitation in our case?

Calcualted Cells (only in Enterprise Edition)
------
I didn't fully understand this one in the documentation, not having spent a lot of time. We do have a number of calculations such as the Revenue/Expense, Margin calculations, etc. Is this an important feature that I should consider?

Partitions:
----
I doubt that we will be partitioning across multiple servers (may not need distributed partioning, linked cubes). However, within a single database/instance, do I have the option to partition the cube (does it help to do so for cube manageability) if I went with Standard Edition? Also, given the large fact tables, I may want to incrementally add monthly data to the cube. Would it help to partition the cube in this case and, do I need Enterprise Edition for this?

HTTP Internet Support: (Enterprise Edition only)
-------
I would think that the HTTP access is applicable to intranet applications (ours is one). Does this support in Enterprise Edition automatically means that the data is available for analysis over the web? What tool would I use to access the data (other than the third party tools such as Crystal Analysis, Brio, etc)? I am trying to see what this means and how this relates to my decision on Std Vs Enterprise Editions.

I really appreciate any input you may have on this.

Thanks in advance
Murthy JarugumilliI would suggest to adopt Enterprise Edition as you already know the limitaitons of Standard edition.

In general Standard edition is used as a database server for a small workgroup or department.