Menu
I have a Table in MYSql called companies and each companies has a type say type 1, type 2 type 3,
example :
id company_name company_type
===============================
1 test1 3
2 xyz 2
3 ashdasdjk 2
4 test 4 1
5 test 3
6 ahsdkjsg 1
7 TCS 2
and so on ...
now i want to write a query to fetch results such that i get 20 companies of type 1, 20 companies of type 2 and 20 companies of type 3... i mean i want to fetch maximum of 20 companies of each type
I am using Codeigniter..
297
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count. The value of both the parameters can be zero or positive integers.
The SQL LIMIT clause restricts how many rows are returned from a query. The syntax for the LIMIT clause is: SELECT * FROM table LIMIT X;. X represents how many records you want to retrieve. For example, you can use the LIMIT clause to retrieve the top five players on a leaderboard.
To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement.
SELECT * FROM ( SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records.
select * from (
select
c.*,
@rn := if(company_type != @ct, 1, @rn + 1) as rownumber,
@ct := company_type
from
companies c
, (select @rn := 0, @ct := null) var_init
order by
company_type
) comp
where rownumber <= 20;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
