Solved

SQL 2000 GROUP BY OR DISTINCT CLAUSE

Posted on 2006-06-21
3
855 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
3 Comments
 
LVL 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now