How to distinct the record when using Group By?

Hallo, I am having the table (look below), which having 4 records. Notice that ColumnA and ColumnB are having the same value, and ColumnC and columnD will have different value.

ColumnA ColumnB ColumnC ColumnD
------- ------- ------- -------
xx      yy      AAA     333
xx      yy      BBB     555
xx      yy      AAA     333
xx      yy      BBB     555

I was trying to select the whole record using Group By quer开发者_JS百科y like this:

SELECT ColumnC from TableA GROUP BY ColumnC;

This query only shows me ColumnC but my expectation is to select the whole record not only ColumnC.

UPDATE: My expected output is:

ColumnA ColumnB ColumnC ColumnD
------- ------- ------- -------
xx      yy      AAA     333
xx      yy      BBB     555

May I know how can I do this?

THanks @!

You could put all of the columns in your SELECT and GROUP BY clauses:

SELECT 
    ColumnA, ColumnB, ColumnC, ColumnD 
FROM 
    TableA 
GROUP BY
    ColumnA, ColumnB, ColumnC, ColumnD

This would basically be equivalent to

SELECT DISTINCT
    *
FROM
    TableA

but is more explicit. As has been pointed out by OMG Ponies, the syntax can vary between DBMSs. In some you may be able to simply do:

SELECT * FROM TableA GROUP BY ColumnC

In Oracle:

SELECT  *
FROM    (
        SELECT  t.*,
                ROW_NUMBER() OVER (PARTITION BY columnC ORDER BY columnA) AS rn
        FROM    mytable
        )
WHERE   rn = 1

Change the ORDER BY clause to control which of the records holding the duplicate will be returned (now that with the least value of columnA is).

If you are using MS SQL the following will get you what you need: SELECT ColumnC,* from TableA GROUP BY ColumnC;

in Oracle, I believe that select ColumnC, TableA.* from TableA GROUP BY COLUMNC; will get you there.

SELECT * from TableA GROUP BY ColumnC;

You would want to select all of the columns and you then need to group by with Column C being first: SELECT * FROM TableA GROUP BY ColumnC, ColumnA, ColumnB, ColumnD