List a number of fields

[zensunni]

I've often wanted to just list the first 5 or 10 fields in a select statement without typing their names out all the time. If I use "*", it's usually too wide for the sql client.

Is there any way to just say "SELECT <the first five fields> from mytable"?

Thanks for any help.

[debasisdas]

If wishes were horses i would ride on to moon.

[nomad]

look what the where clause means

ie
Select * from db
WHERE .......
or
SELECT ID FROM id
WHERE

[Atli]

As far as I know this isn't possible using a standard SELECT query.

But... I was kind of bored so I made a procedure that simulates such a behavior:
[code=mysql]
DROP PROCEDURE IF EXISTS `GetSample`;
DELIMITER %%
CREATE PROCEDURE `GetSample`(
IN db VARCHAR(64),
IN tbl VARCHAR(64),
IN colCount INT(2),
IN cond VARCHAR(512)
)
BEGIN
DECLARE doLoop BOOLEAN DEFAULT TRUE;
DECLARE currentCol VARCHAR(64);
DECLARE colCursor CURSOR FOR
SELECT `COLUMN_NAME`
FROM `INFORMATION_SC HEMA`.`Columns`
WHERE `TABLE_SCHEMA` = db
AND `TABLE_NAME` = tbl;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doLoop = FALSE;

SET @cols = '';
SET @colIndex = 0;

OPEN colCursor;
WHILE doLoop DO
SET @colIndex = @colIndex + 1;
FETCH colCursor INTO currentCol;
IF doLoop THEN
IF @colIndex > 1 THEN
SET @cols = CONCAT(@cols, ', ', currentCol);
ELSE
SET @cols = currentCol;
END IF;
END IF;
IF @colIndex >= colCount THEN
SET doLoop = FALSE;
END IF;
END WHILE;
CLOSE colCursor;

SET @query = CONCAT('SELECT ', @cols, ' FROM ', db, '.', tbl, ' ', cond);
PREPARE stmt FROM @query;
EXECUTE stmt;
END%%
DELIMITER ;

[/code]
Which can be used like:

mysql> CALL GetSample('test', 'testdata', 4, '');
+------+--------+------+-----------------+
| ID   | Name   | Age  | Occupation      |
+------+--------+------+-----------------+
|    1 | Joe    |   20 | Plumber         |
|    2 | Doe    |   25 | Pilot/Bartender |
|    3 | Jane   |   18 | Student         |
|    4 | Tarsan |   28 | Model           |
|    5 | Bob    |   65 | President       |
+------+--------+------+-----------------+
5 rows in set (0.02 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> CALL GetSample('test', 'testdata', 2, 'WHERE Age > 20');
+------+--------+
| ID   | Name   |
+------+--------+
|    2 | Doe    |
|    4 | Tarsan |
|    5 | Bob    |
+------+--------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

Maybe not exactly what you were looking for but it does the job :)