Use MySQL to query in order except the first record

Databases
1

Hello there.

I need to extract data from a MySQL DB table in CSV format.

My stored procedure below that works correctly in exporting the csv file, but the data sorting is wrong, because instead of sorting as in the query it inserts the column headers at the end of all the data instead of inserting the headers on the first row .

I also tried using the syntax

CASE WHEN count_access = "count_access" THEN 0 ELSE CONVERT(count_access, UNSIGNED) END DESC

without solving the problem.

sProc

CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN

DECLARE start_date DATE;
DECLARE end_date DATE;

SET start_date = MAKEDATE( YEAR ( now()), 1 );
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY );

SET @`outfull` := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-' , DATE_FORMAT(end_date,'%d_%m_%Y'), '_' , 'export.csv'); 
									
FLUSH HOSTS;										
	
SET @`qry` := CONCAT('SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	INTO OUTFILE \'', @`outfull`, '\' 
	FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC;');    

PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

END
2

you’ve managed somehow to confuse the forum’s syntax hilighter, so congratulations on that…

try

'SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC
)

(That way the query cant get confused about what you’re trying to ORDER, and what table it’s pulling from for which bit.)

PS: You’re not limiting your query to start date and end date. So… uhm… yeah. about that file name…

3

Thanks for reply.

I have edit the sProc without success… it inserts the column headers at the end of all the data instead of inserting the headers on the first row .

CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN

DECLARE start_date DATE;
DECLARE end_date DATE;

SET start_date = MAKEDATE( YEAR ( now()), 1 );
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY );

SET @`outfull` := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-' , DATE_FORMAT(end_date,'%d_%m_%Y'), '_' , 'export.csv'); 
									
FLUSH HOSTS;										
	
SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC
) q; ');    

PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

END
4

… try wrapping the second half of the query in parenthesis too? i suppose its possible mysql is still confused and is ordering the union rather than the part.

5

Thanks, but now:

  1. the column headers are at the top of the CSV file
  2. the column count_access sorting is random and not from largest to smallest
SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	(SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC)
) q; ');
6

So as you convert the column, which type is it and what does the data look like? Did you try to use CAST() instead?

7

Thanks for reply… I tried using CAST without success

SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	(SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CAST(`count_access` AS UNSIGNED) DESC)
) q; ');
8

Show US some sample data

9

Okay, thanks

-- ----------------------------
-- Table structure for `dotable`
-- ----------------------------
DROP TABLE IF EXISTS `dotable`;
CREATE TABLE `dotable`  (
  `Start_date` date DEFAULT NULL,
  `End_date` date DEFAULT NULL,
  `users` varchar(50) DEFAULT NULL,
  `count_access` int(11) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `users`(`users`) USING BTREE
) ENGINE = MyISAM;

-- ----------------------------
-- Records of `dotable`
-- ----------------------------
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'NMADDALE', 2255, 1);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'ANTAS', 1873, 2);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'MORGONGIOR', 1554, 3);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'DIGA', 33, 4);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'SUGARDU', 544, 5);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'CARDEDU', 1332, 6);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'SBARTOLO', 761, 7);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'POGGIOPINI', 1168, 8);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'BITTI', 1211, 9);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'BOLOTANA', 1192, 10);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'ARBUS', 1032, 11);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'MATTARGHE', 1225, 12);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'INGEGNERIA', 2066, 13);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'CANALFON', 810, 14);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'BAREGA', 617, 16);
10

So why do you need the cast? It’s already an integer

11

They are tests, but the ordering continues to be wrong…

SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	(SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY `count_access` DESC)
) q; ');
12

This may be off topic, but if the frequency of converting is low, it may be an option to split the task using an ordinary SQL query and then convert the raw SQL to ,csv using Javascript, PHP or whatever??

Like this pseudocode:

const query = 'SELECT * FROM your_table';
connection.query(query, (err, results, fields) => {
  if (err) throw err;

  // Convert the result set to a CSV string
  let csvString = '';
  const headers = fields.map(field => field.name).join(',') + '\n';
  csvString += headers;

  results.forEach(row => {
    const rowValues = fields.map(field => {
      const value = row[field.name];
      // Escape special characters if needed
      return typeof value === 'string' ? `"${value.replace(/"/g, '""')}"` : value;
    }).join(',');
    csvString += `${rowValues}\n`;
  });

  console.log(csvString);
  connection.end();
});
13

Odd, it works for me…

14

For me the result looks wrong too

15

Oh sorry, I was focussing on the headers since thats what the OP originally reported. Derp.

16

This is really strange. event this does not work:

SELECT * FROM
    (
    
    SELECT
	1 AS rr, 
    'Start_date',
	'End_date',
	'users',
	'count_access' 
    UNION ALL
	SELECT
	2 AS rr,
    `Start_date`,
	`End_date`,
	`users`,
	count_access
	FROM dotable) s
	ORDER BY rr, count_access DESC
17

A Union dumps sorts in its subqueries.

(I spent an embarassingly long time trying to figure out why I couldn’t CAST AS INT. My MYSQL brain not engaged today.)

18

Ok, I got it

because the first value is not an int but the string “count_access” mySQL is not able to order it.
If you change the code to

SELECT
	1 AS rr, 
    'Start_date',
	'End_date',
	'users',
	3  as count_access
    UNION ALL
	SELECT
	2 AS rr,
    `Start_date`,
	`End_date`,
	`users`,
	count_access
	FROM dotable
	ORDER BY rr, count_access DESC

it works.

So this is a solution (maybe not the best but it works)

    SELECT 'Start_date',
	'End_date',
	'users',
	CASE WHEN count_access = -1 THEN 'count_access' ELSE count_access END 
    FROM
    (SELECT
	1 AS rr, 
    'Start_date',
	'End_date',
	'users',
	-1  as count_access
    UNION ALL
	SELECT
	2 AS rr,
    `Start_date`,
	`End_date`,
	`users`,
	count_access
	FROM dotable
	ORDER BY rr, count_access DESC) s
19

Thanks a lot all for this help.
Really appreciated

SET @`qry` := CONCAT('SELECT * INTO OUTFILE \'', @`outfull`, '\' FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (( SELECT ''Start_date'', ''End_date'', ''users'', ''count_access'' ) UNION
( SELECT `Start_date`, `End_date`, `users`, IFNULL( `count_access`, 0 ) count_access FROM `dotable` )
ORDER BY
CASE		
		WHEN `count_access` = "count_access" THEN
		999999 ELSE CAST( `count_access` AS SIGNED ) 
	END DESC) q;');    

PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

Start_date	End_date	users	count_access
01/01/2024	09/06/2024	NMADDALE	2255
01/01/2024	09/06/2024	INGEGNERIA	2066
01/01/2024	09/06/2024	ANTAS	    1873
01/01/2024	09/06/2024	MORGONGIOR	1554
01/01/2024	09/06/2024	CARDEDU 	1332
01/01/2024	09/06/2024	MATTARGHE	1225
01/01/2024	09/06/2024	BITTI	    1211
01/01/2024	09/06/2024	BOLOTANA	1192
01/01/2024	09/06/2024	POGGIOPINI	1168
01/01/2024	09/06/2024	ARBUS	    1032
01/01/2024	09/06/2024	CANALFON	 810
01/01/2024	09/06/2024	SBARTOLO	 761
01/01/2024	09/06/2024	BAREGA	     617
01/01/2024	09/06/2024	SUGARDU	     544
01/01/2024	09/06/2024	DIGA	      33
20

To ensure that the CSV file is correctly formatted with headers on the first row and the data sorted appropriately, we need to adjust the stored procedure slightly. The main issue with the original procedure is that the UNION ALL operation places the header row as part of the overall result set, which can cause issues when combined with the ORDER BY clause.

Instead, we’ll generate the CSV file in two steps:

  1. Write the headers into the file.
  2. Append the sorted data to the same file.

Here’s the revised version of your stored procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN

DECLARE start_date DATE;
DECLARE end_date DATE;

SET start_date = MAKEDATE(YEAR(NOW()), 1);
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY);

SET @outfull := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-', DATE_FORMAT(end_date,'%d_%m_%Y'), '_', 'export.csv');

-- Write the header
SET @header_query := CONCAT('SELECT
    \'Start_date\' AS `Start_date`,
    \'End_date\' AS `End_date`,
    \'users\' AS `users`,
    \'count_access\' AS `count_access`
INTO OUTFILE \'', @outfull, '\'
FIELDS TERMINATED BY \';\' LINES TERMINATED BY \'\r\n\';');

PREPARE stmt FROM @header_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Append the data
SET @data_query := CONCAT('SELECT
    `Start_date`,
    `End_date`,
    `users`,
    IFNULL(`count_access`, 0)
INTO OUTFILE \'', @outfull, '\' 
FIELDS TERMINATED BY \';\' LINES TERMINATED BY \'\r\n\'
FROM `dotable`
ORDER BY CONVERT(`count_access`, UNSIGNED) DESC;');

PREPARE stmt FROM @data_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;

Explanation

  1. Header Query: The first query writes only the header row into the CSV file.
  2. Data Query: The second query appends the sorted data to the same CSV file. Note the use of INTO OUTFILE for both queries, but the file path remains the same, ensuring the data is appended after the header.

This approach ensures the headers appear first, followed by the correctly sorted data rows.