CODEWITHSUNDEEP
mysqlsql
Naix
Naix

Reputation: 17

How to select record with biggest value of column

Columns: numberIWant, value

I would like to get numberIWant number of records, which have biggest value.

select max(value) from myTable

only returns the max value.

I want to find numberIWant that have this 'max' value. How to do it?

Upvotes: 0

Views: 43

Answers (3)

Naix
Naix

Reputation: 17

Shlomi's anwser work. Verace's not worked for my problem, in my case i can't use 'value' to find 'numwant'

I found another (and easy) answer it is:

SELECT numIWant
FROM myTable
ORDER BY value DESC
LIMIT 1

but I have never seen if anyone use statement like this, is it wrong or has low performance in some cases? or can i use it everytime?

Upvotes: 0

Shlomi Noach
Shlomi Noach

Reputation: 9354

Use GROUP_CONCAT as follows:

SELECT 
  MAX(value),
  SUBSTRING_INDEX(
    GROUP_CONCAT(numberIWant ORDER BY value DESC),
    ',', 1
  )
FROM
  my_table

A lot of queries of this sort don't actually require a self join and can be satisfied with a single SELECT.

Upvotes: 1

Vérace
Vérace

Reputation: 908

A perhaps simpler approach to Rolando's is shown in the SQL below. As to which is better is difficult to say, as the performance may vary according to your circumstances. Depending on how big the table is, the performance will vary according to your PRIMARY KEY and indexes. I can't test with a realistic dataset, so any testing in that area is up to you.

If value (I've called it myvalue) is UNIQUE (and NOT NULL), then you don't need to put IN and just = will do instead. This should be more performant. If myvalue is the PRIMARY KEY, it should be even faster still.

BTW, welcome to the forum! Next time, could you give us DDL (CREATE TABLE blah(col1 col1_type, col2...) and some DML (INSERT INTO blah VALUES(col1_value, col2_value...);). This makes it easier for those trying to help you to create test cases for our answers.

This forum is a great resource, so help us to help you and check out the post here (and links within) about how to ask great questions. 

CREATE TABLE `numwant` 
(
  `numiwant` int(11) DEFAULT NULL,
  `myvalue` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> SELECT * FROM numwant;
+----------+---------+
| numiwant | myvalue |
+----------+---------+
|        2 |       5 |
|        3 |     456 |
|        4 |      55 |
|        4 |     456 |
+----------+---------+
4 rows in set (0.00 sec)

mysql> 

mysql> SELECT n.* FROM numwant n WHERE n.myvalue IN (SELECT MAX(myvalue) FROM numwant);
+----------+---------+
| numiwant | myvalue |
+----------+---------+
|        3 |     456 |
|        4 |     456 |
+----------+---------+

Upvotes: 0

Related Questions