LIMIT SPECIFIC LINES in MYSQL-CodePudding

I'm trying to limit a table's display to one occurrence of a specific word in a row.

I have, let's say :

test, 21, Service, Youtube
test, 23, Service, Google
test, 24, Out, Youtube
test, 24, Out, Duck

And I want only one occurrence of the lines containing 24, and all the other lines. So I'd have :

test, 21, Service, Youtube
test, 23, Service, Google`
test, 24, Out, Youtube

This is just an example, the table is way bigger, but it's for the sake of clarity. Thanks in advance,

CodePudding user response：

There is no way of establishing first but a simple union may do

drop table if exists t;
create table t(col varchar(50));
insert into t values
('test, 21, Service, Youtube'),
('test, 23, Service, Google'),
('test, 24, Out, Youtube'),
('test, 24, Out, Duck');


select * from
(select t.* from t where instr(col,'24') > 0 limit 1) a
union  all
select t.* from t where instr(col,'24') = 0
;

 ---------------------------- 
| col                        |
 ---------------------------- 
| test, 24, Out, Youtube     |
| test, 21, Service, Youtube |
| test, 23, Service, Google  |
 ---------------------------- 
3 rows in set (0.001 sec)

CodePudding user response：

Find Nth number of results from the query

Use TOP or LIMIT

for eg. select TOP 3 from table or select * from table limit 3

SELECT TOP 1 SALARY  
FROM (  
      SELECT DISTINCT TOP 3 SALARY  
      FROM tbl_Employees  
      ORDER BY SALARY DESC  
      ) RESULT  
ORDER BY SALARY