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
189
votes
12 answers

Get top n records for each group of grouped results

The following is the simplest possible example, though any solution should be able to scale to however many n top results are needed: Given a table like that below, with person, group, and age columns, how would you get the 2 oldest people in each…
Yarin
  • 173,523
  • 149
  • 402
  • 512
29
votes
4 answers

Define a variable within select and use it within the same select

Is there a possibility to do something like this? SELECT @z:=SUM(item), 2*@z FROM TableA; I always get NULL for the second column. The strange thing is, that while doing something like SELECT @z:=someProcedure(item), …
user2370579
  • 321
  • 1
  • 4
  • 7
12
votes
3 answers

Cannot set a global variable on MySQL

I'm using MySQL in localhost (in ubuntu and also in windows). I want to set a global variable, and I have tried in all ways but even though I get an "ok" message from mysql, then when I do the "select @var" it allways says "NULL". I've tried: set…
leonardo_palma
  • 303
  • 2
  • 4
  • 12
9
votes
6 answers

Optimizing COUNT(DISTINCT) slowness even with covering indexes

We have a table in MySql with arround 30 million records, the following is table structure CREATE TABLE `campaign_logs` ( `domain` varchar(50) DEFAULT NULL, `campaign_id` varchar(50) DEFAULT NULL, `subscriber_id` varchar(50) DEFAULT NULL, …
Rams
  • 2,141
  • 5
  • 33
  • 59
9
votes
2 answers

PHP and MySQL showing different results with same query

I have a MySQL query which works fine when executed directly on my local MySQL Database, but shows a different result when executed via PHP. SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count FROM 0_lychee_albums AS a LEFT…
tobi
  • 1,924
  • 2
  • 20
  • 25
6
votes
6 answers

Show the default value for a variable

I am using SET GLOBAL = to modify dynamic settings in mysql and I am wondering if there is some way to get the default value for each variable? For instance, if I use the following: SET GLOBAL max_connections = 1000; and then…
Cyclonecode
  • 29,115
  • 11
  • 72
  • 93
6
votes
3 answers

my sql use a variable in the ALTER TABLE ADD COLUMN statement

Hi I am looking to create a table with date as column name. I am using this code to Add column to the table: DROP PROCEDURE IF EXISTS filldates; DELIMITER | CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE) BEGIN WHILE dateStart <= dateEnd…
Abdulla
  • 441
  • 1
  • 9
  • 21
6
votes
2 answers

select random value based on probability chance

How do I select a random row from the database based on the probability chance assigned to each row. Example: Make Chance Value ALFA ROMEO 0.0024 20000 AUDI 0.0338 35000 BMW 0.0376 40000 CHEVROLET 0.0087 15000 CITROEN …
Dharman
  • 30,962
  • 25
  • 85
  • 135
6
votes
3 answers

How to show all user-defined variables (MySQL)

I set two user-defined variables as shown below but after some time, I forgot the names: SET @a = 2, @b = 3; So, does MySQL have the command that displays all user-defined variables?
fidel150992
  • 303
  • 5
  • 17
6
votes
2 answers

MySQL: Count of records with consecutive months

I've searched around for this, but all the similar questions and answers are just different enough not to work. I have a table with the following fields: person, thing, purdate. A new record is entered when a person buys each new thing. I want to…
Crit
  • 73
  • 1
  • 4
4
votes
1 answer

Am I calculating maximum connection limit on our MySQL db correctly?

I have a MySQL database in production and I am trying to calculate the optimised number to set the MySQL max_connections value to - Am I doing this correctly as my sums seem quite high to me. As far as I can tell the logic from the link below is as…
Zabs
  • 13,852
  • 45
  • 173
  • 297
3
votes
1 answer

Node.JS + MySQL - Combining queries for defining vars and selecting data

I have a query that works perfectly in PHPMyAdmin: SET @row_number=0; SELECT * FROM blah blah @row_number blah blah; However, when I put this into Node.JS, I can't use a simple con.query("SET @row_number=0; SELECT * FROM blah blah @row_number blah…
user9375768
3
votes
1 answer

MySQL query to get a particular rank

I have a query on MySQL database working in a such way that I get this result: > Rank Gold Silver Bronze Total > 1 76 78 77 231 > 2 4 5 6 15 > 3 4 1 1 6 > 4 3 0 …
Idro
  • 253
  • 1
  • 7
3
votes
1 answer

Combining condition from two columns mysql

I would like to combine conditions from 2 different columns for my query. This is my original query. You can test it in sqlfiddle.com. -- creating database first for test data create table attendance(Id int, DateTime datetime, Door char(20)); INSERT…
EDDY
  • 103
  • 1
  • 10
3
votes
3 answers

Rank users in mysql by their points

I am trying to rank my students by their points that I've calculated before but the problem is if students have same points they both should be in same rank E.g Student 1 has full points Student 2 has full points they both have to be rank as…
Anar Bayramov
  • 11,158
  • 5
  • 44
  • 64
1
2 3 4 5 6 7 8