Mysql min function returning incorrect result

Question

I have Book_Details table

CREATE TABLE `Book_Details` (
  `bookId` int(11) NOT NULL,
  `book_name` varchar(45) DEFAULT NULL,
  `book_desc` varchar(45) DEFAULT NULL,
  `price` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And this table contains three rows

insert into book_details values (1,'Java for beginners','Java Book for beginners',99);
insert into book_details values (2,'Learn Advanced java','Advanced Java Book',223);
insert into book_details values (3,'Learn Practical java','Practical Java Book',78);

While I am executing the below query

select min(price) min_price,book_name from book_details;

I was expecting below result

min_price | book_name
-----------------------
78        | Learn Practical java

but surprisingly I am getting below output

min_price | book_name
-----------------------
78        | Java for beginners

Could someone please explain me why I am getting this output? Where I am wrong? My MYsql DB version is 5.5.38. Please help.

Gordon Linoff · Accepted Answer · 2015-04-26 20:43:31Z

4

You get the minimum price and arbitrary values for the other columns. You are using a MySQL extension that you should just simply stop using -- always be sure that all columns in the select that are not arguments to aggregation functions -- are in the group by.

Try this instead:

select bd.*
from book_details
order by price
limit 1;

answered Apr 26, 2015 at 20:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

phylae Over a year ago

Note that even with this example, if multiple rows have the same price, the SQL standard doesn't guarantee which row you'll get. If you want consistency, include a unique column in the ORDER BY clause. If you don't have a unique column, you should probably add one (and make it the primary key).

fredt · Accepted Answer · 2015-04-26 20:48:55Z

0

This SELECT statement is not proper SQL but MySQL accepts it. The query always returns a single row, but several books may have the same price and the query could return any of the book_name values. You should use a SELECT that returns multiple rows for all the books that have the minimum price

select price,book_name from book_details where price = (select min(price) from book_details)

answered Apr 26, 2015 at 20:48

fredt

24.5k3 gold badges43 silver badges63 bronze badges

Collectives™ on Stack Overflow

Mysql min function returning incorrect result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related