MySQL Forums
Forum List  »  Newbie

Re: Query LEFT JOIN
Posted by: Angel Rivero
Date: August 22, 2014 09:31AM

Rick James Wrote:
-------------------------------------------------------
> The subquery that does the ranking needs an ORDER
> BY clause.

thank you, you mean this?
mysql> DROP TABLE
IF EXISTS `test`;

CREATE TABLE `test` (
	`LCD` CHAR (6) DEFAULT NULL,
	`NumberCode` INT (11) DEFAULT NULL,
	`CodeDates` date DEFAULT NULL,
	`Codex` CHAR (10) DEFAULT NULL,
	`Q` DECIMAL (10, 5) DEFAULT NULL,
	`zonOp` CHAR (4) DEFAULT NULL,
	`ID` INT (11) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`ID`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP0',
		'RSID',
		'XM7',
		0.02400,
		51015524,
		'2014-6-18'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP1',
		'RSID',
		'XM7',
		1.54400,
		47266370,
		'2014-6-18'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP2',
		'RSID',
		'XM7',
		0.83500,
		48742114,
		'2014-6-13'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XI6',
		0.13000,
		29721250,
		'2014-6-11'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.30200,
		29721253,
		'2013-12-20'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.68700,
		38680929,
		'2014-6-18'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.79500,
		39415749,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XG6',
		0.00600,
		40240767,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XI6',
		0.09000,
		40240769,
		'2014-6-17'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.05600,
		40240770,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.50400,
		42624322,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.51600,
		42797920,
		'2014-6-17'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.61000,
		45180544,
		'2014-6-17'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.24900,
		45494706,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.25200,
		45541676,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.03300,
		46309158,
		'2014-6-16'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XM7',
		0.30300,
		46348630,
		'2014-6-25'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP3',
		'RSID',
		'XE5',
		0.06800,
		47542502,
		'2014-6-17'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP5',
		'RSID',
		'123',
		0.06800,
		47542502,
		'2014-6-17'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP6',
		'RSID',
		'123',
		0.06800,
		47542502,
		'2014-6-17'
	);

INSERT INTO `test` (
	`LCD`,
	`zonOp`,
	`Codex`,
	`Q`,
	`NumberCode`,
	`CodeDates`
)
VALUES
	(
		'RSIDP7',
		'RSID',
		'XXX',
		0.06800,
		47542502,
		'2014-6-17'
	);

SELECT
	tmp.LCD,
	tmp.NUMBERCODE,
	tmp.codex,
	tmp.rank
FROM
	(
		SELECT
			LCD,
			NUMBERCODE,
			codex,

		IF (
			@prev <> LCD,
			@rownum := 1,
			@rownum := @rownum + 1
		) AS rank,
		@prev := LCD
	FROM
		test t
	JOIN (
		SELECT
			@rownum := NULL,
			@prev := 0
	) AS r
	WHERE
		(
			Q > 0
			AND Codex IS NOT NULL
			AND NumberCode IS NOT NULL
			AND CodeDates NOT IN ('0000-00-00')
		)
	AND zonOp = 'RSID'
	ORDER BY
		LCD,
		NUMBERCODE
	) AS tmp
JOIN tbl_CB CB ON tmp.Codex = cb.Codex2
WHERE
	tmp.rank <= 10
GROUP BY
	LCD,
	NumberCode
ORDER BY
	LCD,
	NUMBERCODE;

+--------+------------+-------+------+
| LCD    | NUMBERCODE | codex | rank |
+--------+------------+-------+------+
| RSIDP0 |   51015524 | XM7   |    1 |
| RSIDP1 |   47266370 | XM7   |    1 |
| RSIDP2 |   48742114 | XM7   |    1 |
| RSIDP3 |   29721250 | XI6   |    1 |
| RSIDP3 |   29721253 | XM7   |    2 |
| RSIDP3 |   38680929 | XM7   |    3 |
| RSIDP3 |   39415749 | XM7   |    4 |
| RSIDP3 |   40240767 | XG6   |    5 |
| RSIDP3 |   40240769 | XI6   |    6 |
| RSIDP3 |   40240770 | XM7   |    7 |
| RSIDP3 |   42624322 | XM7   |    8 |
| RSIDP3 |   42797920 | XM7   |    9 |
| RSIDP3 |   45180544 | XM7   |   10 |
+--------+------------+-------+------+
13 rows in set

mysql> 

Options: ReplyQuote


Subject
Written By
Posted
August 08, 2014 11:24AM
August 08, 2014 02:13PM
August 09, 2014 03:49AM
August 09, 2014 07:54AM
August 09, 2014 10:46AM
August 21, 2014 05:31AM
August 21, 2014 12:16PM
Re: Query LEFT JOIN
August 22, 2014 09:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.