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

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?

CodePudding user response：

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

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.