Stack Overflow
Stack Overflow

Questions tagged [mysql-variables]

For use with special handling of queries for such things as ranking, grouping, and segmenting. The use of cross-joins that initialize variables, Producing otherwise difficult or impossible queries without them. One should not use this tag merely if you are using variables, in simply fashion.

Pertaining to special use-cases where MySQL User-Defined Variables play an essential or unique role in solving queries or other related MySQL functionality.

These User-Defined variables start with an @ sign, are not DECLARED, and are session-specific. The variables are destroyed upon the close of the connection.

The variables often play a crucial role in special functionality such as ranking, grouping, and special ordering of results. Some of these use-cases include crafting tricky queries to achieve certain, challenging, resultsets.

108 questions
1
vote
1 answer

Error on "User Defined Variable" assigned as dynamic Field Alias

I have created MySQL query interpolation below in Workbench. The query was so fit to my needs except that I could not effect the "User Defined Variable" @var1/2/3 as my field aliases. I am getting an error on AS @var1 in this line (SELECT field4…
RickyBelmont
  • 619
  • 4
  • 11
1
vote
3 answers

How to fetch rows from which sum of a single integer/float column sums upto a certain value

I have a table. It has the following structure goods_receiving_items id item_id quantity created_at I am trying to fetch rows against which have the following conditions Has one item_id When the sum of the quantity column equals a certain…
Abid Suleman Ahmed
  • 156
  • 1
  • 11
1
vote
0 answers

Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)

Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql. Let's assume the following statement update three rows: UPDATE table_name SET a=1 WHERE id IN (1, 2, 3); In mysql 5.7.21: The…
Leonardo Kuffo
  • 901
  • 9
  • 10
1
vote
1 answer

Update single column of table and value should start from 0 and then should increment by 1

$sqlcount1 ="SET uid = -1 UPDATE `agent_normal` SET `uid` = @a:=@a+1"; $result = mysqli_query($conn,$sqlcount1); how to write this query in core php? This query works in mysql but not in core php. Here i want to update a single row of table and…
Mohsin
  • 199
  • 5
  • 19
1
vote
1 answer

pass variable into where mysql

Cannot get the query to run, throwing error on the @searchin variable. Probably very simple but cannot see it. set @search = "chip"; set @searchin = "CompanyName"; select * from con_search where @searchin like concat ('%',@search,'%')
user3767402
1
vote
1 answer

Why does MySQL 5.7 order by affect temporary rank column?

I need to generate a list of employees ranked by percent of closings. I built the following stored procedure: SET @cnt = 0; SET @percent = 2.0; SELECT CASE WHEN stats.close/(stats.open+stats.close) = @percent THEN @cnt ELSE…
Joseph Derblich
  • 43
  • 6
1
vote
1 answer

mysql - select recent activity per user into column

Edit - I found it different from pivot table. Because in my case, it based on date I have table with linked data. I want to return the most recent activity per customer into column, like temporal data for data mining purpose. So I have these columns…
Rietaros
  • 11
  • 3
1
vote
0 answers

mysql query to do rank function

i want to write a MySQL query to rank scores. If there is a tie between two scores, both should have the same ranking. also after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes"…
sritharan
  • 41
  • 1
  • 10
1
vote
3 answers

Why the order of evaluation for expressions involving user variables is undefined?

From MySQL Manual the output of the following query is not guaranteed to be same always. SET @a := 0; SELECT @a AS first, @a := @a + 1 AS second, @a := @a + 1 AS third, @a := @a + 1 AS fourth, @a := @a + 1 AS fifth, @a := @a + 1 AS…
Anonymous One
  • 411
  • 2
  • 6
  • 16
1
vote
1 answer

MySQL variables in nested subqueries

Currently I'm working on kind of e-learning platform which contain courses and exams module. The idea is that course is followed by exam. User can solve exam few times (depending on setting in exam table). I have a module where I need to determine…
Rocket
  • 130
  • 2
  • 14
1
vote
2 answers

How can I speed up this query with an aliased column?

So I found this code snippet here on SO. It essentially fakes a "row_number()" function for MySQL. It executes quite fast, which I like and need, but I am unable to tack on a where clause at the end. select @i:=@i+1 as iterator, t.* from …
MikelG
  • 459
  • 4
  • 16
1
vote
1 answer

MySQL complex subquery formulation

I have two tables - books and images. books has columns like id, name, releasedate, purchasecount. images has bookid (which is same as the id in books, basically one book can have multiple images. Although I haven't set any foreign key constraint),…
SexyBeast
  • 7,913
  • 28
  • 108
  • 196
1
vote
1 answer

Return multiple rows from multi SELECT subquery

query SELECT (SELECT NAME FROM product_component) AS pcNAME, (SELECT PROJECT_NAME FROM jira_project) AS jpNAME, (SELECT FILTER_NAME FROM jira_filter) AS jfNAME Each SELECT will return an indeterminate number of rows. I get the error…
wmash
  • 4,032
  • 3
  • 31
  • 69
1
vote
1 answer

MySQL Query get the last N rows per Group

Suppose that I have a database which contains the following columns: VehicleID|timestamp|lat|lon| I may have multiple times the same VehicleId but with a different timestamp. Thus VehicleId,Timestamp is the primary key. Now I would like to have as…
nikosdi
  • 2,138
  • 5
  • 26
  • 35
0
votes
0 answers

MySQL - partition table by key field and select latest row ordered by event column as view

I am running MySQL Community Server v5.7.40 and have 2 tables: "customers" and "events" created as follows: create table customers (customer_id int, name varchar(100), primary key (customer_id)); create table events (event_id int, customer_id int,…
nick_j_white
  • 534
  • 6
  • 27
1 2
3
4 5 6 7 8