CODEWITHSUNDEEP
sql-server
yoda
yoda

Reputation: 121

only display one row when key field is the same

I have created a key field (C) by joining two columns(A&C). I want to run an sql that says, if column C is unique take only the top row.

Sample data:-

A                B                C                  D
10022            Blue             10022Blue          Buggy
10300            Red              10300Red           Noodle
10300            Red              10300Red           Sammy

so I only want one line to show for 10300Red

Cheers

Upvotes: 0

Views: 41

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

One way to do it is with a cte and ROW_NUMBER():

;WITH CTE AS
(
    SELECT A, 
           B, 
           C, 
           D, 
           ROW_NUMBER() OVER(PARTITION BY C ORDER BY (SELECT NULL)) rn
    FROM Table
)

SELECT A, B, C, D
FROM CTE
WHERE rn = 1

Note: You did say you want the "first" record, but you didn't specify the order of the records. Since tables in a relational database are unsorted by nature, "first" is simply an arbitrary row, hence "order by (select null)"

Upvotes: 1

neer
neer

Reputation: 4082

You can find the result set by grouping it, then join it with the main table.

SELECT
    A.*
FROM
    YourTable A INNER JOIN 
    (
        SELECT
            G.C,
            MAX(G.D) D
        FROM
            YourTable G
        GROUP BY
            G.C
    ) B ON A.C = B.C AND A.D = B.D

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Do it this way:

select distinct A, B, C from tablename

Upvotes: 0

Related Questions