Newbie: Distinct values

[steve]

Hi,
this is very simple.

I have a table with:

Name
Num1
number2
Date
Value1
etc.

I want to extract all distinct Date values ONLY and keep the rest of the
information.
SELECT DISTINCT Name, Date, .......
will consider the whole row, but that's not what I want.

e.g.: from a a table:
item_A 100 0001 9/2/02 blah....
item_B 250 0001 9/3/02 n/a
item_D 80 0002 9/3/02 n/a
item_B 90 0002 8/8/02 n/a

will give:
item_A 100 0001 9/2/02 blah
item_B 250 0001 9/3/02 n/a
(missing....)
item_B 90 0002 8/8/02 n/a

where as SELECT DISTINCT will not have filtered anything because all lines
are distinct (in all fields together).

As long as we are about to have a duplicate Date we discard the row.
SIMPLE!

why the heck i cannot find an answer.
ANY help will be greately appreciated.
-steve

 

[Tom Ellison]

Dear Steve:

In your example, you have "discarded" item_D for the date 9/3/02 and
kept item_B for that same date. What is the "rule" for which one is
kept and which discarded?

This "rule" must be coded into the query, and the rule must produce a
way to filter the data (a unique key within each date) so that only
one row meets the criterion in that rule for each date.

A process like this cannot generally be arbitrary, nor is it usually
desirable that it be arbitrary.

Your statement:

"As long as we are about to have a duplicate Date we discard the row.
SIMPLE!"

is not necessarily simple. The statment "about to" seems to imply
there is an order in which the data must be considered, and you want
to keep the first row for each date and discard the rest. If I've
interpreted this correctly, what is that order? Possibly, the
solution to this lies within the answer to that question.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[steve]

Dear Tom,
You are absolutely right!

Well, there is no rule! what i mean to say is that take the "first come" row
and discart the rest.
The reasons are a bit too complicated to explain.
(One of the fields is a meteo-station ID and the rest meteorological
measurements. Once in a while they overlap measuring devicies so we end up
with same : date, parameter code and different measurements. I want to be
able to tell the user that for a range of dates I HAVE or HAVE NOT a
measurement for this date, this parameter and this station. In other words i
have to simply count distinct dates AND show the rest of the info. I am
using VB.net and dont feel like storing in array and go through an algorithm
to count properly. fast and dirty is good enough......well i guess i just
gave you a descent idea despite my comment )

That's the reason i discarded the 3rd row in my example. It just came after
i already had this date.

I know its not good programming thinking but i just need to give the user an
*idea* of the percentage of days that we have measurements in a *range* of
dates. that's all.

Hope this is clearer.
Thank you for your effort and time.
-steve
PS: I googled DISTINCT and it seems that is not configurable

 

[Tom Ellison]

Dear Steve:

Would it then work this way:

SELECT DISTINCT [NAME], [Parameter], [DATE]
FROM YourTable

This would tell you which [NAME] (station?) have reported on a given
date.

In the case that there are multiple reports for that Station and
Parameter on a Date, you could show a count of how many you had. What
sense does it make to show the actual measurements, especially if
there may be conflicting measurements?

To do this arbitrarily, you could try a totals query using FIRST() on
the measurement columns. I think this will give you measurements that
all come from the same row. Perhaps that would work for what you
want.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts