Sql Query Arrange Selected Data

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

I'm using MySQL and I want a better query to handle what I am trying to do.

I have an 'options' table which has two columns:

primarykey and options

My userinterface sends the primarykey to php which finds the selected row with

Code: [ Select ]
"SELECT * FROM options WHERE primarykey = '$key'";
$var = $arr1['options'];
  1. "SELECT * FROM options WHERE primarykey = '$key'";
  2. $var = $arr1['options'];


However, I'd like to send that row of data along with the rest of the rows in the table back to my program with the row that matches the primarykey at the front instead of having to follow with another query that selects all and joins the variables to the tail like:

Code: [ Select ]
"SELECT * FROM options"
$var .= $arr['options'];
  1. "SELECT * FROM options"
  2. $var .= $arr['options'];


I'm sure there's an easy SQL method that will selectively place one row in the front and join the rest of the rows to the tail. I'd like to use one query but am unsure if ORDER BY can handle something like this or what because it seems when I select WHERE primarykey = 'somevalue' it's only going to return that one row.
Many thanks in advance.
Moderator Remark: Added code tags
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

SQL Code: [ Select ]
"SELECT * FROM optiones WHERE (SELECT * FROM options) AND primarykey = '$key'"
Try that and tell me what you get... I doubt that would work or something... :roll:
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

I get operand should contain one column. So, I used

Code: [ Select ]
SELECT * FROM options WHERE (SELECT options FROM options) AND primarykey = '$key'



and it returns error #1242:
subquery returns more than one row


I'm wanting the subquery to return more than one row. Seems like the right direction.

I had to query using phpmyadmin but I'll try it in my php script when I get a chance but if it didn't work in phpmyadmin query window then I don't think it's going to populate mysql_fetch_array. right?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

Right... I'll let the SQL gurus help you on this one... I got that SQL from extensive google search for 20 minutes... that's how bad I am at SQLs :roll:
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

Thanks for the attempt. I've been all over google/ozzu to make sure it's not something simple already answered elsewhere but I haven't had any luck so I posted here. Hopefully, one of the gurus will see this post. I usually just go to my texts for something like this, but my SQL book is MIA. I usually never use it cause the basics I know get me thru everything I've been doing as of late. This is a difficult howto subquery operation to find online. Thnks again for your time.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

First an example table.

Code: [ Select ]
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
 
mysql> USE test
Database changed
 
mysql> CREATE TABLE always_first(
   -> pkey INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   -> options INT(8)
   -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO always_first (options)
   -> VALUES (1),(2),(4),(8),(16);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM always_first;
+------+---------+
| pkey | options |
+------+---------+
|    1 |       1 |
|    2 |       2 |
|    3 |       4 |
|    4 |       8 |
|    5 |      16 |
+------+---------+
5 rows in set (0.00 sec)
  1. mysql> CREATE DATABASE test;
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> USE test
  5. Database changed
  6.  
  7. mysql> CREATE TABLE always_first(
  8.    -> pkey INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  9.    -> options INT(8)
  10.    -> );
  11. Query OK, 0 rows affected (0.01 sec)
  12.  
  13. mysql> INSERT INTO always_first (options)
  14.    -> VALUES (1),(2),(4),(8),(16);
  15. Query OK, 5 rows affected (0.00 sec)
  16. Records: 5  Duplicates: 0  Warnings: 0
  17.  
  18. mysql> SELECT * FROM always_first;
  19. +------+---------+
  20. | pkey | options |
  21. +------+---------+
  22. |    1 |       1 |
  23. |    2 |       2 |
  24. |    3 |       4 |
  25. |    4 |       8 |
  26. |    5 |      16 |
  27. +------+---------+
  28. 5 rows in set (0.00 sec)


Let's see if it's as easy as using CONCAT.

Code: [ Select ]
mysql> SELECT CONCAT(
   -> (SELECT options FROM always_first WHERE pkey = 3),
   -> (SELECT options FROM always_first WHERE pkey <> 3)
   -> );
ERROR 1242 (21000): Subquery returns more than 1 row
  1. mysql> SELECT CONCAT(
  2.    -> (SELECT options FROM always_first WHERE pkey = 3),
  3.    -> (SELECT options FROM always_first WHERE pkey <> 3)
  4.    -> );
  5. ERROR 1242 (21000): Subquery returns more than 1 row


FAIL. :(

When working with multiple rows, it looks like we need to use the GROUP BY functions.
http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat

Code: [ Select ]
mysql> SELECT CONCAT(
   -> (SELECT options FROM always_first WHERE pkey = 2),
   -> (SELECT GROUP_CONCAT(options SEPARATOR '') FROM always_first WHERE pkey <> 2)
   -> ) AS result;
+--------+
| result |
+--------+
| 214816 |
+--------+
1 row in set (0.00 sec)
  1. mysql> SELECT CONCAT(
  2.    -> (SELECT options FROM always_first WHERE pkey = 2),
  3.    -> (SELECT GROUP_CONCAT(options SEPARATOR '') FROM always_first WHERE pkey <> 2)
  4.    -> ) AS result;
  5. +--------+
  6. | result |
  7. +--------+
  8. | 214816 |
  9. +--------+
  10. 1 row in set (0.00 sec)
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

Interesting. I like it. It should work when I get a chance to use it as it looks like you got the result I'm looking for. It's even a little different than the mysqldev example from the link you provided. I don't see where they wrapped the two subqueries in SELECT CONCAT() which I assume makes this whole thing work for my purpose. Very cool twist on the group-concat. Thanks for you help. This gets me moving forward with much less code and I'm going to continue reading over group-concat functionality.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

I only read enough to get something working, which is why I included the link for you. :)
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

How about the following? (I just found out about this SQL function today :roll: although I've read about it before and just forgotten about it)
SQL Code: [ Select ]
"SELECT * FROM options WHERE primarykey = '$key'
UNION
SELECT * FROM options"
  1. "SELECT * FROM options WHERE primarykey = '$key'
  2. UNION
  3. SELECT * FROM options"
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

Good work Bogey. You got it. The UNION call is better for what I need. It seems the GROUP_CONCAT only got one(1) total rows and made a single string out of the results. Did you guys get that same outcome with the GROUP_CONCAT, which makes sense because it is concatenate? Both ways are good to know.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

Awesome :D Glad to know that it helped you fix it :)

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 40 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.