Show first occurance only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with ID and DtH fields. There may be more than one of the
same ID. I want to have a query just show me only the first occurance of each
ID and I want to be able to update the DtH field for the first occurance.

ID DTH
23320 0 *
23320 0
48702 0 *
49066 0 *

Only want to see and be able to update the * rows.

Any ideas?

Thanks in advance for your help!
 
Liz C said:
I have a table with ID and DtH fields. There may be more than one of the
same ID. I want to have a query just show me only the first occurance of each
ID and I want to be able to update the DtH field for the first
Click to expand...
occurance.

Liz C.,

There is no "first" inherent in a table. You must make an ordering
via the ORDER BY clause. So, what ORDER are we looking for (the
data in the columns subsequent to ID do not suggest much)?
ID DTH
23320 0 *
23320 0
48702 0 *
49066 0 *
Click to expand...

There are three columns of data in this space delimited list, but
only two column names. May I ask what the name of the third column
is?
Only want to see and be able to update the * rows.
Click to expand...

That's easier:

SELECT ID
,DTH
,UnknownColumnName
FROM UnknownTableName
WHERE UnknownColumnName = "*"

UPDATE UnknownTableName
SET <a column> = <some expression>
WHERE UnknownColumnName = "*"


Sincerely,

Chris O.
 
Dear Liz:

I agree with the principles given you by Steve and Chris.

If you only want to see ID and DTH values, you could uses:

SELECT DIDTINCT ID, DTH
FROM YourTable

This eliminates duplicates from within ALL the columns shown. If you add
more columns, you may then have more than one row showing for a given pair
of ID and DTH values, that is, if there is more than one value in the added
column.

This technique eliminates"duplication" but does nothing to choose one row as
"first" over all the others. It simply combines all identical rows of the
selected values.

Tom Ellison
 
That makes sense. Okay, if I add the field ID to the query and select max of
that, it pulls the results I want, but I want to be able to update the dth
for these fields.
 
Link that query to the original data table, by both the ID and Max() result
field. Show all fields, and you should be able to edit.
 
Back
Top