mre531s
asked on
SQL 2000 GROUP BY OR DISTINCT CLAUSE
I have a table with several columns. I want to SELECT DISTINCT records based on two of the columns values. I also need to retrieve the value of a third column but I don't want it used in the DISTINCT virtual table.
This query gives me the data I need:
SELECT Column1, Column2
FROM Table1
GROUP BY Column1, Column2
I also need in Column3 in my result. If I use this query I get an error because it's not used in the GROUP BY Statement
SELECT Column1, Column2, Column3
FROM Table1
GROUP BY Column1, Column2
Any suggestions?
This query gives me the data I need:
SELECT Column1, Column2
FROM Table1
GROUP BY Column1, Column2
I also need in Column3 in my result. If I use this query I get an error because it's not used in the GROUP BY Statement
SELECT Column1, Column2, Column3
FROM Table1
GROUP BY Column1, Column2
Any suggestions?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The MAX FUNCTION Will work
Column1 Column2 Column3
John Doe 1
John Doe 2
John Doe 8
The following Example using MAX() will give me a result of
John Doe 8
I only need 1 value from column 3 as a return value to update the table
Thanks!!!
Column1 Column2 Column3
John Doe 1
John Doe 2
John Doe 8
The following Example using MAX() will give me a result of
John Doe 8
I only need 1 value from column 3 as a return value to update the table
Thanks!!!
(select column1, column2 from table1 group by column1, column2) b
where a.column1 = b.column1
and a.column2 = b.column2