sort my database data

I have a database table like this

<table>
 <tr>
   <th>no</th>
   <th>name</th>
   <th>income</th>
   <th>work</th>
   <th>score</th>
 </tr>
 <tr>
   <td>1</td>
   <td>alan</td>
   <td>US 100</td>
   <td>Designer</td>
   <td>30%</td>
 </tr>
 <tr>
   <td>2</td>
   <td>lara</td>
   <td>US 100</td>
   <td>Doctor</td>
   <td>34%</td>
 </tr>
 <tr>
   <td>3</td>
   <td>foong</td>
   <td>US 100</td>
   <td>Teacher</td>
   <td>7%</td>
 </tr>
 <tr>
   <td>4</td>
   <td>Rose</td>
   <td>US 50</td>
   <td>Waitress</td>
   <td>55%</td>
 </tr>
 <tr>
   <td>5</td>
   <td>Jack</td>
   <td>US 30</td>
   <td>Programmer</td>
   <td>50%</td>
 </tr>
 <tr>
   <td>6</td>
   <td>jeff</td>
   <td>US 3</td>
   <td>Lawyer</td>
   <td>90%</td>
 </tr>
 <tr>
   <td>7</td>
   <td>elson</td>
   <td>US 2000</td>
   <td>Footballer</td>
   <td>60%</td>
 </tr>
 <tr>
   <td>8</td>
   <td>steve</td>
   <td>US 22</td>
   <td>chef</td>
   <td>10%</td>
 </tr>
 <tr>
   <td>9</td>
   <td>albert</td>
   <td>US 35</td>
   <td>Admin</td>
   <td>80%</td>
 </tr>
 <tr>
   <td>10</td>
   <td>warnock</td>
   <td>US 5</td>
   <td>Technicer</td>
   <td>1%</td>
 </tr>
 <table>

开发者_JAVA百科

then I want show the result on the table is Top 5 Score only but my table is sort by income column.

I use this string to query:

 SELECT * FROM table ORDER by score DESC, income ASC LIMIT 0,5 

but my result is sort by score not income.

I want the table is sort by income with top 5 score only

any idea on this?

thanks for advance.

---

i think your asking how to limit the results sent back from the sql?

put ORDER by score LIMIT 0,5 at the end of your query

EDIT:

SELECT * FROM table ORDER by score,income DESC LIMIT 0,5 

---

Just treat your initial result set as a sub-query of one in which you sort your final results by income:

SELECT  *
FROM    (
    SELECT * FROM table ORDER by score DESC, income ASC LIMIT 0,5 
    ) t
ORDER BY income

---

If you what to show the top 5 score you have to sort the table with 'score' not with income. You can use LIMIT 0,5 to list the top 5 into the table.

---

I you want to use data return from sql query then put ORDER BY score LIMIT 0, 5 at end of your sql query.

Or if you want to sort the data in HTML table, you should do that manaually.

Best of Luck. :)

---

Do you have a unique column on your table if yes you may try this.

SELECT t2.* 
 FROM (SELECT 
              uniqueId, score 
         FROM yourTable 
        ORDER BY score DESC 
        LIMIT  0,5) as t1
  INNER JOIN yourTable t2 
     ON t2.uniqueId = t1.uniqueId 
  ORDER BY t2.income ASC;

Try to execute the query first on your SQL browser to see if the results are correct.

Note: if score is not a numeric data type or if it is a char, this will sort alphabetically. So 10 is came before 2 in ascending order. You can put score + 0 in case of this, I have not yet try this to other data type.

One more thing using LIMIT 0,5 will only get 5 rows. Then base on your question you want to display the top five score. So in case you have a 10 rows with same score and it is the top score the other five will not included.

Ex. 15 person with a score of: 10,9,8,7,7,7,7,7,7,7,4,4,4,3,1 in my point of view all the person with a score greater than 4 is in the top 5. So LIMIT 0,5 is not the way in case of this. I hope this can help you.

---

SELECT * FROM table ORDER by score DESC, income ASC LIMIT 0,5

Maybe you should write this:

SELECT * FROM table ORDER by income ASC, score DESC LIMIT 0,5