How to group by on selected column in sql?

Question

How to apply a group by condition in a selected column I want to apply group by condition only on name field of the "userdata" table.

SELECT CAST(UserId AS varchar(255)) AS UserId,
       CAST(name AS varchar(255)) AS name,
       CAST(city AS varchar(255))
FROM "userdata"
WHERE name = 'Jhohn'
GROUP BY CAST(UserId AS varchar(255)),
         CAST(name AS varchar(255)),
         CAST(city AS varchar(255));

The result of the above is

userId  name       city 
1       jhon       florida
2       sam        new york
3       sam        wales
4       thomas     new york

Expected results:

userId  name       city 
1       jhon       florida
2       sam        new york
4       thomas     new york

Possible duplicate of [sql server select first row from a group](https://stackoverflow.com/questions/7344731/sql-server-select-first-row-from-a-group)? — Thom A, Jan 22 '19 at 11:15
How should that work? Which value of userID do you expect for "sam"? There a 2 and a 3. The same applies for the city field. — D. Mika, Jan 22 '19 at 11:16
Possible duplicate of sql server select first row from a group? – Larnu in the given link the fields another than that to be grouped is required to be aggregated. — Ocean, Jan 22 '19 at 11:18
@Ocean You can see my answer below. Here Min() function is used to keep only to skip 3rd row and keep 1st row. You can use Max() function if you want to keep 3rd row and remove first row. — Suraj Kumar, Jan 22 '19 at 12:27

PSK · Answer 1 · 2019-01-22T11:41:55.067

1

You can use ROW_NUMBER() like following to achieve what you are looking for

SELECT * FROM
    (
       SELECT userid, 
           NAME, 
           city, 
           Row_number() OVER(partition BY NAME ORDER BY id) RN 
    FROM   userdata 
    )T
    WHERE  T.RN= 1

I am not sure why you are casting VARCHAR data again to VARCHAR? and you are putting a where condition in you sample query which will filter the records and you will not get all the records.

edited Jan 22 '19 at 11:41

answered Jan 22 '19 at 11:22

PSK

17,547
5
32
43

where clause will execute before select statement and you alias RN in select statement. I am consfused :) – Zafor Jan 22 '19 at 11:40

score 1 · Answer 2 · answered Jan 22 '19 at 11:47

You can use like the below one.

DECLARE @Table TABLE (UserId int, [Name] varchar(255), City varchar(255));
INSERT INTO @Table (UserId,[name], city)
VALUES(1,'Jhon', 'florida') 
,(2,'sam', 'New york')
,(3,'sam','wales')
,(4,'thomas','new york');

SELECT sq.userId, sq.Name, sq.City
FROM
(
    SELECT *, DENSE_RANK() OVER(PARTITION BY t.Name ORDER BY t.UserId) AS RNo
    FROM @Table AS t
) AS sq
WHERE sq.RNo = 1

score 1 · Accepted Answer · answered Jan 22 '19 at 12:21

You can also do this using following code

DECLARE @Table TABLE (UserId int, [Name] varchar(255), City varchar(255));
INSERT INTO @Table (UserId,[name], city)
VALUES(1,'Jhon', 'florida') 
,(2,'sam', 'New york')
,(3,'sam','wales')
,(4,'thomas','new york');

Select t.* from @Table t
inner join (
SELECT MIN(UserId) as UserId, Name
FROM   @Table
GROUP BY
       Name
)a on t.UserId = a.UserId

You can find the live demo Here

How to group by on selected column in sql?

3 Answers3