How To Group By Multiple Columns In Sql Server

I understand how GROUP BY works and I also understand why my query does not bring the results I am expecting. However, what would be the best way to eliminate duplicates in this ca

Solution 1:

Here is a solution which uses a subquery to identify the "first match" from the Person table, which I have interpreted to mean the person with the lowest id value in each city group.

SELECT t1.Id,
       t1.Name AS PersonName,
       t2.Name AS CityName
FROM Person t1
INNER JOIN City t2
    ON t1.CityId = t2.Id
INNER JOIN
(
    SELECT CityId, MIN(Id) AS minId
    FROM Person
    GROUPBY CityId
) t3
    ON t1.CityId = t3.CityId AND t1.Id = t3.minID

There is probably also a way to do this with window functions.

Solution 2:

A Partition By City and a Sub-Query should do the trick:

SELECT R.ID, R.PERSON_NAME, R.CITY_NAME FROM
(
    SELECT P.ID, P.NAME [PERSON_NAME], C.NAME [CITY_NAME],
             ROW_NUMBER() OVER (PARTITIONBY C.ID ORDERBY P.ID) AS rn
    FROM Person P
    INNERJOIN CITY C
    ON P.CITYID = C.ID
) R
WHERE R.rn =1

Result:

1       John Smith      Seattle
4       Bruno Davis     Los Angeles
7       Tom Walker      San Francisco

Solution 3:

If above not working thant try distinct,

SELECT tbl.Id,
       tbl.PersonName,
       tbl.CityName
FROM
(
   SELECT c.Id, c.Name as PersonName, p.Name as CityName
   FROM City c
   INNER JOIN Person p ON p.CityId = c.Id
   ORDERBY c.Name, p.Name
) AS tbl
GROUPBY tbl.PersonName

edited

Here is query,

SELECTDISTINCT c.Id, c.Name as PersonName, p.Name as CityName
   FROM City c
   INNER JOIN Person p ON p.CityId = c.Id
   ORDERBY c.Name, p.Name