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
0
votes
3 answers

How to rank a record with same weight in MySQL

Suppose I have a data say: Name | Marks StudentA | 90 StudentB | 85 StudentC | 85 StudentD | 70 now StudentA will get 1st Rank, StudentB and StudentC will get 2nd Rank and Student D will get 4th Rank. I know the basic rank computation if…
komal sarvaiya
  • 13
  • 2
0
votes
1 answer

Applying SQL limit on a particular column, not on complete result set

In the below given resultset where there are 2 unique users (id) and there could be more such users coming out of the query: Here is the multi-join query: select id, name, col1Code, col2Code, col2Value from users_table UT join …
Bhavya Narula
  • 42
  • 5
0
votes
0 answers

User Defined Variable to rank items as most recent

I'm trying to pull information from the most recent 20 box scores for players on a particular team. I look up the teams roster, and pull relevant information for all the games those players have played in. I want the most recent 20 games of a…
tim blackburn
  • 1
  • 1
0
votes
3 answers

mysql grouped ranking with ties

so i have data in a table like this: id total group_id 1897 738 1 2489 716 2 2325 715 3 1788 702 2 1707 699 3 2400 688 3 2668 682 2 1373 666 1 1494 666 …
GOA
  • 91
  • 1
  • 9
0
votes
1 answer

Executing a stored procedure with cursor in PHP

I have a stored procedure that I am trying to call from my php. Here is the stored procedure: BEGIN DECLARE done INT DEFAULT FALSE; declare phone_temp VARCHAR(20) default ''; declare phone_cur cursor for SELECT DISTINCT sentNum FROM Queue; declare…
starvator
  • 989
  • 1
  • 11
  • 26
0
votes
1 answer

Mysql: Update using select and local variable

I am looking for an update statement that will group terms by language in the following table CREATE TABLE _tempTerms( ID int(8) unsigned NOT NULL AUTO_INCREMENT, TTC_ART_ID mediumint(8) unsigned, TTC_TYP_ID mediumint(8) unsigned, …
user2634392
0
votes
3 answers

top 10 by category mysql

I have a table where I have to get top 10 in each category based on the amount spent by a person in that category. My sample data is person category amount roger perfume 20 jim perfume 50 joe tv 5 jim tv 25 kathy car …
ash
  • 781
  • 1
  • 9
  • 20
0
votes
1 answer

how to combine two tables having different column name

I have two tables say A and B . table A: +------------+ | manager_id | +------------+ | 100 | | 102 | | 103 | | 124 | | 149 | | 101 | | 201 | | 205 | +------------+ Table:B +------------+ |…
user2623213
  • 233
  • 2
  • 4
  • 10
-1
votes
1 answer

I want the highest and the lowest value of a table, why can't I save that value in PHP?

$category = htmlspecialchars($_GET['category']); $sql = "(SELECT number FROM german WHERE german.category_german LIKE ".$category." ORDER BY number DESC LIMIT 1) as 'high', (SELECT number FROM…
FreakyLord
  • 21
  • 3
-1
votes
2 answers

How to store a result of a row in a variable and use it in the next row in MySQL SELECT query?

I have the following tables: Stocks id name amount 1 Pen 35 2 Cd 21 3 Bag 15 StockUnits id name include stockid 1 onepen 1 1 2 dozen 6 1 3 pocket 24 1 4 onecd 1 2 5 pocket 5 2 6 onebag 1 3 So how do I get a…
Hamreen Ahmad
  • 522
  • 5
  • 21
-1
votes
1 answer

why do i get error Must declare the scalar variable "@x". while the query works in the sql window?

the error that I'm getting is " Must declare the scalar variable "@x"." I'm trying to declare a variable @x and make him a NVARCHAR(50) then id like to subtract an amount from it called @z and finally update a Table in the server called Stock with…
roee oselka
  • 21
  • 3
-1
votes
2 answers

Catch stored procedure output into a variable without using out params

Consider the following procedure: CREATE PROCEDURE `getFoo`() BEGIN select 'bar' as foo; END; Once call-ed, it outputs: {"foo":"bar"} But what if getFoo is invoked within a different stored procedure, how do I catch its result content into a…
Constantin
  • 848
  • 8
  • 23
-1
votes
1 answer

MySQL running total only working with hard coded integer

I have the following SQL which is not working how I thought it would. SET @runtot:=0; select q1.week, (@runtot := @runtot + cast(q1.flying_hours as SIGNED)) as flying_hours, q1.flying_hours as weekly_hours from ( select date_format(d.date,"%x%v")…
PrestonDocks
  • 4,851
  • 9
  • 47
  • 82
-1
votes
3 answers

Having trouble with mysql variables, what am I doing wrong here?

See below and test here. I've been pulling my hair out over this for a couple of hours now. I've searched many posts and as best I can tell everything is correct. I'm having trouble with the IF comparison and the @malefemale variable. I can pull…
mal
  • 3,022
  • 5
  • 32
  • 62
-1
votes
1 answer

SUM from the results of a subquery of N results as max for each user

Let's suppose this schema: CREATE TABLE test ( test_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, user_Id INT NOT NULL, date DATE, result VARCHAR(255) NOT NULL, ) engine=innodb; My goal is to pick up the last 5 results as maximum for each different…
Dez
  • 5,702
  • 8
  • 42
  • 51
1 2 3 4 5 6
7
8