unique values with GROUP BY

[jamesm6162]

Hi

I'm trying to create a view containing 3 fields, all from the same table.

Here's what I have:

SELECT MAX(dateField), field2, field3
FROM table1
GROUP BY field2, field3

Basically I want the highest dateField for each field2, and then just add the corresponding field3 next to it. But this query doesnt give me unique values for field2

If I leave the field3 out of the query as follows:
SELECT MAX(dateField), field2
FROM table1
GROUP BY field2
then I get unique field2 values, but i dont have the corresponding field3 value.

How should I rewrite this query to achieve what i'm trying to?

Thanks

[Zoidal]

Try this:

SELECT MAX(dateField), DISTINCT(field2), field3
FROM table1
GROUP BY field2, field3

I don't know if the MAX and DISTINCT may conflict (or go to first field2 value in table) so check to see if output is correct, but your problem is that your original query is looking at all various combinations of field2 and field3 to find MAX, so DISTINCT says only use field2 once for each value in field2.

[jamesm6162]

I've tried it, but it gives a syntax error. I don't think you can use DISTINCT in that manner.

[chazzy]

This is the natural behavior of a group by, it groups it by distinct tuples (if more than one column is specified in the group by clause).

What if you did something like this?


select
columns...
from your_table yt
inner join
(select max(dateField) as mDateField, idField as mIdField from your table group by field2) m
on m.mIdField = yt.idField

[jamesm6162]

Well i discovered that I had some duplicates that I had two eliminate first. But then this query should work. I also figured out an alternative, though.

Thanks for the help