Retrieve data from mysql based upon a priority

Question

I have 10 records in MySQL and suppose I want that 4th record should be on top and the remaining 9 are retrieved after the 4th.

Select * from info <what to put here> ??

I tried using this code but it's wrong

SELECT * FROM profiles WHERE sno = 30 ORDER BY sno UNION ALL SELECT * FROM profiles WHERE sno = 30 ORDER BY sno

Could you please add an example, or an extract of the database — kaldoran, Mar 17 '17 at 14:10
http://prntscr.com/el4597 suppose i want to give priority to this record that it should be on top of the results which are retrived ,, how to do it ? — Counter Aim, Mar 17 '17 at 14:17
SELECT * FROM info WHERE sno = 30 ORDER BY sno UNION ALL SELECT * FROM info WHERE sno = 30 ORDER BY sno; — Alex, Mar 17 '17 at 14:20
@CounterAim why are you referencing images of code? that should be an edit to your question and using real code and not images of them. — Funk Forty Niner, Mar 17 '17 at 14:27
`mysql_fetch_array expects parameter 1 to be resource` error on line 326, look up that error. — Funk Forty Niner, Mar 17 '17 at 14:29
your code is incomplete as per the `WHILE` loop. You're going to have to debug your code. — Funk Forty Niner, Mar 17 '17 at 14:32
SELECT * FROM profiles WHERE sno = 30 UNION SELECT * FROM profiles WHERE sno != 30; — Alex, Mar 17 '17 at 14:36
$query = mysql_query("SELECT * FROM profiles WHERE sno = 30 ORDER BY sno UNION ALL SELECT * FROM profiles WHERE sno = 30 ORDER BY sno;"); — Counter Aim, Mar 17 '17 at 14:36
@CounterAim: use this: $query = mysql_query("SELECT * FROM profiles WHERE sno = 30 UNION SELECT * FROM profiles WHERE sno != 30;"); — Alex, Mar 17 '17 at 14:38
will this work ? $query = mysql_query("SELECT * FROM profiles WHERE sno = 30 AND sec = 'Y' AND title = 'Secretariat ' UNION SELECT * FROM profiles WHERE sno != 30 AND sec != 'Y' AND title != 'Secretariat ';"); — Counter Aim, Mar 17 '17 at 14:43

Alex · Answer 1 · 2017-03-17T14:35:33.843

0

SELECT * FROM info WHERE id = 4
UNION
SELECT * FROM info WHERE id != 4;

Later edit, based on new information

SELECT * FROM profiles WHERE sno = 30
UNION
SELECT * FROM profiles WHERE sno != 30;

edited Mar 17 '17 at 14:35

answered Mar 17 '17 at 14:14

Alex

436
4
9

UNION will probably reorder the result set. You should at least use UNION ALL. However a specific order is never guaranteed withot an ORDER BY clause. – Paul Spiegel Mar 17 '17 at 14:15

kaldoran · Answer 2 · 2017-03-17T14:21:09.127

0

Try this one :

select * from info where sno = 4
UNION ALL 
select * from info where sno <> 4

First, I select only the row number 4, then I just add all the remaining rows by using <> which means different of. The UNION ALL do the union between the 2 select

edited Mar 17 '17 at 14:21

answered Mar 17 '17 at 14:18

kaldoran

845
6
19

`!=` is valid in MySQL. So your answer is a copy. – Paul Spiegel Mar 17 '17 at 14:19

Bojan Radaković · Answer 3 · 2017-04-20T08:09:17.207

0

If you are sure that you need exactly the 4th element, just get the data from the database:

$sql = "SELECT * FROM info;";

Then while printing print $elemets[3] first (as $elements[0] is the first row that is fetched) and then do unlink($elements[4]); and print the rest of the elements.

edited Apr 20 '17 at 08:09

answered Mar 17 '17 at 14:18

Bojan Radaković

430
3
12

Paul Spiegel · Answer 4 · 2017-03-17T14:28:58.103

0

You can use a boolean expression for the order. sno = 30 will return 1 for that specific row and 0 for all other rows. So you can use it for descending order.

Select * 
from info
order by sno = 30 desc, sno

edited Mar 17 '17 at 14:28

answered Mar 17 '17 at 14:22

Paul Spiegel

30,925
5
44
53

Retrieve data from mysql based upon a priority

4 Answers4