Filtering Majority Count

Question

Currently, my query output is yielding multiple users with user_name column. I want to filter, showing only the majority user listed (Top 1) from the group.

Is this the full query? Your `WHERE` is either missing something or is incomplete. — Steve-o169, May 09 '19 at 12:16
Basically, I've got two users, one of which there are more data entries and is the user I would want to capture. — Diego83, May 09 '19 at 13:27
@Harry: I've added some expectations in the original posting at the bottom for reference. My thought is to filter the majority by majority user. — Diego83, May 09 '19 at 14:09
wouldn't it be as simple as user_name, max(mede_num_value) from your_table group by user_name _ doing this in your data set rather than the front end — Harry, May 09 '19 at 20:40
@Harry: At which position in the data set would this code belong? Select or where positions. — Diego83, May 09 '19 at 21:23
@Diego83 Your expected results only show 2 columns out of 9. Not sure what data is in your other columns! Group by goes after where and you need to group on all columns that are NOT aggregated in the select. I.E. if you don't have a Max , Min, Avg etc around the columns in select, then you need to add that column in the group by. Makes sense? Give it a go in your SQL statement.. — Harry, May 09 '19 at 21:49
@Harry: I understand. I've added another snapshot of the dataset. I have duplicate rows with different user_names. One user is the majority of the data set with the exception of those duplicates. Will the group by filter this out, keeping the user I want to display? — Diego83, May 10 '19 at 04:29

score 0 · Answer 1 · answered May 10 '19 at 12:16

0

I have tried to work on your requirement from sql point of view. How about we restrict data coming from database as only those records for example joe user 2 data should only appear. rather than working on ssrs side i.e after complete data is loaded shall be a painful process. I have created a fiddle for sql. Could you please take a look at it so that you get Idea how to restrict data.

Here is Fiddle link : http://sqlfiddle.com/#!9/8f78c5/2

Code below: Schema

-- borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
  ('1', '3', 'The earth is like a ball.'),
   ('2', '2', 'One hundred angels can dance on the head of a pin');

SQl query below

select * from  `docs` where id=(select id from `docs` group by id order by count(id) desc LIMIT 1)

answered May 10 '19 at 12:16

AnkUser

5,421
2
9
25

I get the following error: Msg 1033, Level 15, State 1, Line 44 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. – Diego83 May 10 '19 at 18:32
(Limit is invalid) Select * from PVXMIHS Where PVXMIHS.USER_NAME=(Select PVXMIHS.USER_NAME from PVXMIHS group by Count(PVXMIHS.USER_NAME) order by COUNT(PVXMIHS.USER_NAME)desc – Diego83 May 10 '19 at 18:33
Try to go one step at a time. How about fire a inside query at first which will give you username order by highest of username count. For ex it will give you John user2 at first row and then John user 1 and so on. Once you get this result try to get the top 1 ie only username with highest count I.e John user 2 only and then pass this username to your select statement with condition where username = username you received from inside query. – AnkUser May 11 '19 at 09:38
I haven't been able to solve this item yet. My subquery is not working. Its not pulling in the value. – Diego83 May 14 '19 at 18:03

Filtering Majority Count

1 Answers1