MySQL data sort

Question

I have a table that has four columns: id, item_number, feature, value.

The table looks like this and has about 5 million entries.

╔════╦═════════════╦═════════╦═══════╗
║ id ║ item_number ║ feature ║ value ║
╠════╬═════════════╬═════════╬═══════╣
║ 1  ║ 234         ║ 5       ║ 15    ║
║ 2  ║ 234         ║ 3       ║ 256   ║
║ 3  ║ 453         ║ 5       ║ 14    ║
║ 4  ║ 453         ║ 4       ║ 12    ║
║ 5  ║ 453         ║ 7       ║ 332   ║
║ 6  ║ 17          ║ 5       ║ 88    ║
║ 7  ║ 17          ║ 9       ║ 13.86 ║
╚════╩═════════════╩═════════╩═══════╝

How can I sort the table so that I can get the item_numbers in descending order based on the feature value?

I am also selecting other feature numbers with their values but I only want to sort by feature number 5.

What do you mean by item_numbers in descending order based on the feature value (this based part)? — souslov
– souslov, Commented Jul 22, 2015 at 4:53
@suslov I am looking for 17, 234, 453 as the result, as 17 has the highest value of 88 for the feature 5 — user1967599
– user1967599, Commented Jul 22, 2015 at 4:56

souslov · Accepted Answer · 2015-07-22 05:01:03Z

3

Using order by with desc and where clauses:

select `item_numbers`
from `tbl`
where `feature` = 5
order by `value` desc

edited Jul 22, 2015 at 5:01

answered Jul 22, 2015 at 4:55

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikas Umrao · Accepted Answer · 2015-07-22 04:57:02Z

2

You need to do order by first with feature and then with item_numbers

select * from `table` order by `feature`, `item_numbers` desc

answered Jul 22, 2015 at 4:57

Vikas Umrao

2,5801 gold badge17 silver badges23 bronze badges

Comments

Brian Gerhards · Accepted Answer · 2015-07-22 05:06:47Z

0

In your query, add

order by item_number desc

If you are trying to query based on a specific feature, so only receive one set of data for a feature at at time, add

where feature = 'feature'

where "feature" is the feature value you want to search for. If you are looking to provide all features but sort them, you can add

order by feature, item_number desc

and you will be give all features in ascending order and together (grouped) then the items_number(s) in descending order

EDIT:: Sounds like from your latest comment, this may be your solution:

SELECT item_number FROM table WHERE feature = '5' ORDER BY value DESC

edited Jul 22, 2015 at 5:06

answered Jul 22, 2015 at 5:00

Brian Gerhards

8495 silver badges12 bronze badges

Collectives™ on Stack Overflow

MySQL data sort

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related