Geek Answers Handbook

How to distinguish a record when using Group By?

Hallo, I have a table (see below) that has 4 entries. Note that ColumnA and ColumnB have the same value, and ColumnC and columnD will have a different value.

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

I tried to select the entire record using a Group By query as follows:

SELECT ColumnC from TableA GROUP BY ColumnC;

This query shows only ColumnC, but my expectation is to select the entire record, not just ColumnC.

UPDATE: My expected result:

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

Can I find out how I can do this?

Thank @!

+5
source share
5 answers

In Oracle:

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

ORDER BY, , , , (, columnA ).

+2

SELECT GROUP BY:

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


SELECT DISTINCT
    *
FROM
    TableA

. OMG Ponies, . :

SELECT * FROM TableA GROUP BY ColumnC
+4

MS SQL, : SELECT ColumnC,* from TableA GROUP BY ColumnC;

Oracle, , select ColumnC, TableA.* from TableA GROUP BY COLUMNC; .

+1
SELECT * from TableA GROUP BY ColumnC;
0

, C: SELECT * FROM TableA GROUP BY ColumnC, ColumnA, ColumnB, ColumnD

0

More articles:


All Articles