Im stumped on a simple query! I need some help! (1 Viewer)

[killyridols]

Hello,

OK. So I have a table titled NANDAT with the columns: sample_id, shipcode, cruisenum, corenum, depth, data, art

I have a query written shown below:

SELECT DISTINCT sample_id, max(data) AS maxi, sum(data) AS summe
FROM NANDAT
WHERE art<>66 And art<>88 And art<>90 And art<>95 And art<>94 And art<>131 And art<>132 And art<>133
And sample_id Between 1 And 1200
GROUP BY sample_id;

However, I also want to include "art" as a column in the resulting table. I have tried to include it by typing this:

SELECT DISTINCT sample_id, max(data) AS maxi, sum(data) AS summe, art
FROM NANDAT
WHERE art<>66 And art<>88 And art<>90 And art<>95 And art<>94 And art<>131 And art<>132 And art<>133
And sample_id Between 1 And 1200
GROUP BY sample_id, art;

But, this doesn't work. It returns all of the results, not just one record per sample. I only want the record with the max 'data' to show up for each sample. But I also want a 'art' column to appear...

I'm stuck. Help??
Thank you!

 

[pbaldy]

It sounds like you need something like this:

http://www.baldyweb.com/LastValue.htm

For simplicity, you might consider

WHERE art Not In(66,88,90,95,94,131,,132,133)

 

[Brianwarnock]

I think that you need to link this query back to the original table on sample_id to select the art for the sample_id with max(data)

brian

 

[killyridols]

OK so I revised it to this.

SELECT DISTINCT TEST.sample_id, NANDAT.art, maxi, summe
FROM TEST, NANDAT
WHERE art Not In(66,88,90,95,94,131,132,133)
AND TEST.sample_id=NANDAT.sample_id
AND TEST.maxi=NANDAT.data
GROUP BY TEST.sample_id, NANDAT.art, maxi, summe;


This worked except that where there are two of the same MAX values in a sample, both records show up instead of just one. My original query would just select one of them.
Turns out, for my purposes, having multiple MAX values show up is probably more meaningful.
But, could I ask for future reference, how would I eliminate these duplicates, and only show one record per sample Id ?? I thought this is what DISTINCT was for, but it doesn't work in this case.....

Thank you!