How to select First Value In Group By Order by another column

Question

How can select first value of a column order by another column when I'm in a group by ?

My Data:

ID       UserId        HashText          Text          Date
-------------------------------------------------------
1        1000          123               Hi            2018-10-10
2        2000          124               Hello         2018-10-11
3        3000          123               Hi            2018-10-12
4        4000          123               Hi            2018-10-13

My Result:

UserId        HashText      count
-------------------------------------------------------
1000          123           3
2000          124           1

One Note: I cant use min(UserId) - it's not always sort by UserId

Tables does not have fields, they have columns and rows, please edit your question show us what did you try, the actual data and expected result as _formatted text_ not _images_ please. — Ilyes, Oct 18 '18 at 15:49

score 1 · Answer 1 · answered Oct 18 '18 at 16:05

Unfortunately, SQL Server doesn't support first_value() as an aggregation function. But, you can use a trick if you have an aversion to subqueries:

select distinct first_value(userId) over (partition by hashtext order by id) as userId,
       hashtext
       count(*) over (partition by hashtext) as cnt
from mydata;

score 0 · Answer 2 · answered Oct 18 '18 at 15:51

You can use row_number() with ties clause :

select top (1) with ties UserId, HashText, cnt
from table t cross apply
     ( select count(*) as cnt
       from table t1 
       where t1.HashText = t.HashText 
     ) t1
order by row_number() over (partition by HashText order by id);

score 0 · Answer 3 · answered Oct 18 '18 at 15:56

This displays the output.

SELECT CASE WHEN COUNT(userid) >= 1 THEN MIN(userid) ELSE NULL END UserId, b.hashtext, b.count FROM Table t
JOIN (SELECT hashtext, COUNT(userid) COUNT FROM Table t GROUP BY hashtext) b 
ON t.hashtext=b.hashtext 
GROUP BY b.count, b.hashtext

score 0 · Answer 4 · answered Oct 18 '18 at 16:00

Using ROW_NUMBER() OVER(PARTITION BY HashText ORDER BY (UserId) ASC)

Query

CREATE TABLE #temp(ID int,UserId int, HashText  int, Text varchar(5), Datevalue Date)
INSERT INTO #temp(ID,UserId,HashText,Text,Datevalue)
select 1, 1000,123,'Hi'    ,       '2018-10-10' union
select 2, 2000,124,'Hello',        '2018-10-11' union
select 3, 3000,123,'Hi' ,          '2018-10-12' union
select 4, 4000,123,'Hi',           '2018-10-13' 

Select * from #temp 

;with CTE as (
Select UserId,HashText,
ROW_NUMBER() OVER(PARTITION BY HashText ORDER BY (UserId) ASC) sort
FRom #temp
),getCount as 
(SELECT HashText, COUNT(userid) [Count] 
FROM #temp t GROUP BY hashtext)
SElect UserId, C.HashText, [Count] 
FROM CTE C
JOIN getCount T ON
T.HashText = C.HashText
WHERE sort = 1

Output:

How to select First Value In Group By Order by another column

4 Answers4