Why is this SQL table row getting broken up?

Question

As I go through the SQL course, I've been using XAMPP on my laptop to follow along. I made my own table with first and last names and ages and along with an ID number column for the indexing key to experiment on.

Now when I come to the lesson about the MIN() function, someone asks in the comments how you would write it to have the name come up with the MIN() result. In the lesson it was Salary but I tried it with my Age column.

My first thought was to simply check:

SELECT FirstName, LastName, MIN(Age) FROM family;

Now that gave me the unexpected result of the first and last name of the person on the top of my list along with the MIN age which was 20.

That person's age should have been 55, not 20. The person with the MIN age of 20 was on the bottom of the list.

So my question is: How did the oldest person's name get listed along with the youngest person's age? Breaking up a row like that seems quite strange to me...

Answer 1

Min() acts on the whole column, so you will get a name, last name of a particular user and the minimum vale of the Age column (the *whole* column).
This is why you will get a list of all user names followed by the same age value in all rows.

Answer 2

You can also select FirstName, LastName, Age from family order by age limit 1 ;)

Answer 3

Not at my best with SQL but I guess you asked your database to give you the FirstName, LastName and Min(Age)
By default it gave you the min Age you asked for but the first FirstName and LastName it found...
You should try with the WHERE option

SELECT FirstName, LastName, Age FROM family WHERE Age = ( SELECT MIN(Age) FROM family )

Answer 4

Depends on the situation, you might need all the names in the table for whichever reason and only need the min age for an other reason...

Who knows, you might need it some day and be thankfull avout the fact that you wont have to write two SQL request =P

Answer 5

Kuba Siekierzyński Now THERE is the answer I was looking for, thank you.

Now the way I had it, it only gave me the first name on the list. But you say that if I had the entire list then it would have given me the same MIN value on every row? Interesting. I'll have to try that.

I automatically assumed that if you asked for more columns with the MIN value of one of them, that it would know that you wanted the row that that MIN value was from.

Why would someone want that MIN value listed on other rows? Seems odd, I say.

Answer 6

Dyf Dyf Yes, thank you, I did get it to work like that after the first way didn't work but I was surprised enough that it would even break a row up like that, that I still wanted to ask here if anyone knew why it happened.

If I didn't know that that name should not be in a row with that age, I might have thought that it worked 🤔

If someone isn't careful, they could be getting the wrong results back and not even know it...

Answer 7

Kuba Siekierzyński Hmm, I ended up doing it, at the time, the same way that Dyf Dyf mentioned but your way would have been even shorter.

Either way, getting it to work was not my real question, but rather: why did gave me a bad row? As I mentioned to Dyf, I might have thought it worked if I didn't know what name I was supposed to get.

So the question remains...