Solved

SQL 2000 GROUP BY OR DISTINCT CLAUSE

Posted on 2006-06-21
3
860 Views
Last Modified: 2008-03-06
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?

0
Comment
Question by:mre531s
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 16950457
you have to use some aggregate function like this:

SELECT Column1, Column2, max(Column3)
FROM Table1
GROUP BY Column1, Column2

or add the column to the group by
or add the column to the group by and filter/merge the duplicates in the front-end
or use some more complex subquery technique depending on the results you want to have
0
 
LVL 4

Expert Comment

by:indu_mk
ID: 16950560
select a.column1, a.column2, a.column3 from table1 a,
(select column1, column2 from table1 group by column1, column2) b
where a.column1 = b.column1
and a.column2 = b.column2
0
 

Author Comment

by:mre531s
ID: 16950585
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!!!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question