select distinct most frequent value within date range 1

[peac3]

Hello all,

I am trying to select distinct clientid, date, location from the the most frequent value of location from each client within the day.

For example

ClientID     Date       Location
1       2018-05-01         NY
1       2018-05-01         TOK
1       2018-05-01         HK
1       2018-05-01         NY
1       2018-05-01         NY
1       2018-05-01         NY
1       2018-05-01         NY
1       2018-05-02         HK
1       2018-05-02         HK
1       2018-05-02         HK
1       2018-05-02         HK
1       2018-05-02         HK
1       2018-05-02         HK
1       2018-05-02         SG
2       2018-05-02         SYD
2       2018-05-02         SYD
2       2018-05-02         SYD
2       2018-05-02         SG
2       2018-05-02         SG

And expected output should be

ClientID     Date       Location
1       2018-05-01         NY
1       2018-05-02         HK
2       2018-05-02         SYD

Any help would be much appreciated.

Thanks guys

 

[SkipVought]

Hi.

SELECT ClientID, Date, Location, Count(*)
FROM YourTable
GROUP BY ClientID, Date, Location
ORDER BY 4 DESC

Result...
[pre]
ClientID Date Location Expr1003
1 5/2/2018 0:00 HK 6
1 5/1/2018 0:00 NY 5
2 5/2/2018 0:00 SYD 3
2 5/2/2018 0:00 SG 2
1 5/2/2018 0:00 SG 1
1 5/1/2018 0:00 TOK 1
1 5/1/2018 0:00 HK 1
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[johnherman]

Something like this (untested). It's easier to understand if you write the intermediate results to a temp table and then query the temp table.

Select clientID, date, location, max(freq) from
(Select (ClientID+date+location) as Keyfield, ClientID, date, Location, Count(location) as freq from <your table> group by (clientID+ date+location) order by (clientID+date+location), Count(location) descending) group by clientID, date, location


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[DaveInIowa]

I think you will need the RANK function to accommodate ties.

;WITH t AS (
SELECT ClientID, Date, Location,
       RANK() OVER (PARTITION BY ClientID, Date ORDER BY COUNT(*) DESC) FreqRank
  FROM @Table
 GROUP BY ClientID, Date, Location
)

SELECT ClientID, Date, Location
  FROM t
 WHERE FreqRank = 1
 ORDER BY ClientID, Date, Location