How to distinct the record when using Group By?

Question

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 query 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 @!

Answer 1

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

Answer 2

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).