Remove all non-duplicate rows based on a single column in a SQL Server table where there conditions on multiple columns for the select statement

Question

I am attempting to pull data from a SQL Server table that meets certain criteria. Part of that criteria is that there can be multiple rows with the same data in my column and I need all of those rows returned...what I do not want are rows returned that are distinct.

I want to find a session that is in a specific date range, and meets one of two types of action, and are multiple, meaning there are two or more rows for the session.

Example SQL query:

SELECT activity and message 
FROM myTable 
WHERE (date BETWEEN '1/1/2020' and '1/31/2020') 
  AND activity IN ('trace', 'info')

Can you advise how I can grab the rows that meet my criteria of being in the correct date range and with the correct activity, but that have multiple rows only. I want no data that does not meet those three criteria.

Update to Body:

In creating the example query in my initial post, I neglected to include the label column. So the SELECT should read "SELECT activity, label and message FROM myTable WHERE (date BETWEEN '1/1/2020' and '1/31/2020') AND activity IN ('trace','info')". Based on sample data, I would expect the following return:

 activity  message   label 
 --------  -------   -----
 trace     logged    1234 
 info      written   1234

Label '1234' is the only value that meets all criteria: falls in the date range, meets activity values and has multiple rows.

My apologies...here is the expected return. And in creating the example query in my initial post, I neglected to include the label column. So the SELECT should read " SELECT activity, label and message FROM myTable WHERE (date BETWEEN '1/1/2020' and '1/31/2020') AND activity IN ('trace','info')". — jhs
– jhs, Commented May 7, 2020 at 19:19
In creating the example query in my initial post, I neglected to include the label column. So the SELECT should read "SELECT activity, label and message FROM myTable WHERE (date BETWEEN '1/1/2020' and '1/31/2020') AND activity IN ('trace','info')". Based on sample data, I would expect the following return: activity message label trace logged 1234 info written 1234 Label '1234' is the only value that meets all criteria: falls in the date range, meets activity values and has multiple rows. — jhs
– jhs, Commented May 7, 2020 at 19:27
Don't add details in comments. Click the edit button under your question and add the additional information to the body of your question. — Eric Brandt
– Eric Brandt, Commented May 7, 2020 at 19:31

ITHelpGuy · Accepted Answer · 2020-05-07 20:12:01Z

3

With the limited information I could only make a wild guess and see if something like below works for you.

SELECT activity, 
       message,
       COUNT(*) AS Count
  FROM myTable 
 WHERE date BETWEEN '1/1/2020' and '1/31/2020' 
   AND activity IN ('trace', 'info')
   AND message IN ('logged', 'written')
 GROUP BY activity, 
       message
HAVING COUNT(*) > 1
 ORDER BY Count DESC

edited May 7, 2020 at 20:12

answered May 7, 2020 at 19:05

ITHelpGuy

1,0472 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eric Brandt Over a year ago

Bad habits to kick : ORDER BY ordinal. When posting for beginners, it's a good idea to apply best practices to the code you share. But +1 for sorting out the question.

ITHelpGuy Over a year ago

Hi @EricBrandt, Sure I will make a note this.

jhs Over a year ago

Thanks ITHelpGuy...the provided script resolves the issue as I originally posted. I updated the question to include a necessary requirement I neglected to add the first time.

ITHelpGuy Over a year ago

Please find updated code. I guess this should work. If you could add some data to the original post it would be helpful to provide better solution.

Collectives™ on Stack Overflow

Remove all non-duplicate rows based on a single column in a SQL Server table where there conditions on multiple columns for the select statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related