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