Possible to select only 1 row for usernames, even though multiple rows which include usernames are unique

Question

I am trying to select all usernames that have a at least one windows operating system. Some users have many windows operating systems, but I really only need to know if they have at least one.

I know DISTINCT can filter out duplicates, but the issue is these rows are unique, if they have more than one OS. For example:

JohnDoe windows 10;
JohnDoe windows 97;
JohnDoe windows 7;
JennyDoe windows 10;

In this case, JohnDoe will be selected 3 times because he has 3 unique operating systems.

Is there a way to essentially say, if an instance of this username appears, only select one row?

SSMS is an IDE for SQL Server, it doesn't work for MySQL. Please correct your tags. Though your question doesn't appear to be able SSMS at all. — Thom A, Dec 02 '21 at 16:25
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Dec 02 '21 at 16:26
Does this answer your question? [How to select the first row for each group in MySQL?](https://stackoverflow.com/questions/2739474/how-to-select-the-first-row-for-each-group-in-mysql) — Thom A, Dec 02 '21 at 16:27
In your sample data, is e.g. `JohnDoe windows 10` the data in your `username` field, or are there multiple fields, e.g. the field `username` contains `"JohnDoe"` and another field, perhaps `operating_system`, contains `windows 10`? — Bob Jarvis - Слава Україні, Dec 02 '21 at 16:30
One would hope so, @BobJarvis-ReinstateMonica, otherwise the OP has a much larger problem. ;) — Thom A, Dec 02 '21 at 16:35
consider a case expression for your OS `case when OS like '%windows%' then 1 else 0 end as winOS` and then use an analytic `row_number() over (partition by user order by winOS, OS)` as a CTE and then select from the CTE where RN = 1 or use a cross apply... which is a bit more efficient if supported. Assumes analytic is supported... — xQbert, Dec 02 '21 at 16:42
@BobJarvis-ReinstateMonica Ah, yes the data has multiple fields. I'm currently browsing through the other pages Larnu sent me. I think this is a good start. — Ransome, Dec 02 '21 at 16:44

score 2 · Accepted Answer · answered Dec 02 '21 at 18:16

2

The easiest way is to use DISTINCT:

select distinct username
  from your_table
  where operating_system like '%windows%'

Based on the data shown in the question, this will return

JohnDoe
JennyDoe

db<>fiddle here

answered Dec 02 '21 at 18:16

Bob Jarvis - Слава Україні

48,992
9
77
110

or if you need to see one of the operating_system just add a Max(Operating_system) to the select and group by username removing distinct. This is clean. – xQbert Dec 02 '21 at 19:20
Well, I'm sad to give you the answer... mainly because I have used and know distinct. But I simply just only included the users username in the select statement, therefor using distinct would work and did work in this case..... – Ransome Dec 02 '21 at 21:19

score 1 · Answer 2 · answered Dec 02 '21 at 16:57

I assume that username and os - stored in two separate columns.

Let's create the table with some data.

CREATE TABLE IF NOT EXISTS `user_os` ( username varchar(100), os varchar(100));

INSERT INTO user_os values 
('JohnDoe', 'windows 10'),
('JohnDoe', 'windows 97'),
('JohnDoe', 'windows 7'),
('JennyDoe', 'windows 10'),
('Jessica', 'Ubuntu');

Now we can look for 'windows' string inside os column and group by it. You'll get 1 - for users with at least 1 windows os or 0 - for others.

SELECT username, instr(os, 'windows') AS at_least_1_windows
FROM user_os
GROUP BY 1,2;

The result:

username  at_least_1_windows
--------  ------------------
JennyDoe  1
Jessica   0
JohnDoe   1

Sara tabaghchi · Answer 3 · 2021-12-02T23:19:33.790

In sql, you can use Tsql row_number codes and so select only rows that row number is 1 when you want select only one row between many duplicate rows better use this code because distinct is very slow

CREATE TABLE table_name (
    usernam varchar(100),
    os varchar(100),
  

)

insert into table_name
values('john','windows10')

insert into table_name
values('john','windows7')

 insert into table_name
values('david','windows7')

select 
usernam,

    row_number() over (partition by [usernam] order by os  desc)rownum
   
from 

   table_name t

so result is

so if you add this query select only one record 

select tbl.usernam from (
select 
usernam,

   row_number() over (partition by [usernam] order by os  desc) rownum
   
from 

   table_name 
   ) tbl
   
   where rownum=1

so result is

also you can show other fields

select tbl.usernam,tbl.os from (
    select 
    usernam,
os,
       row_number() over (partition by [usernam] order by os  desc) rownum
       
    from 

       table_name 
       ) tbl
       
       where rownum=1

the result is:

according this your query speed is very well.

Possible to select only 1 row for usernames, even though multiple rows which include usernames are unique

3 Answers3