CODEWITHSUNDEEP
phpmysql
Richard
Richard

Reputation: 13

PHP/MySQLi query w/ distict and other parameters

+------------+-----------+----------+
| col1       |    col2   |    col3  |
+------------+-----------+----------+
| Steven     |    12     |    30    |
| Steven     |    10     |    37    |
| Steven     |    16     |    42    |
| Steven     |    11     |    30    |
| Neena      |    14     |    39    |
| Neena      |    11     |    35    |
| Neena      |    9      |    35    |
+------------+-----------+----------+

So I have a table with structure similar to this. I want to get distinct values of col1, but with it also get smallest number of col2 and largest of col3. And also ascending order them by col2. So that the final result would look like this:

Neena 9 39
Steven 10 42

I tried DISTINCT and GROUP BY, but my noobish php/mysql skills doesn't get me results I want. Closest result I get with this exact query:

SELECT `Series`,`YearsFrom`,`YearsTo` FROM `combine_data` WHERE `Make`='$komb' GROUP BY `Series` ORDER BY `YearsFrom` ASC"

But it only gets col2 and col3 of the first col1 row. From the example table my query would return:

Steven 12 30
Neena 14 39

How do I extend my query to get results I want? Maybe query should be different and then processed with php into what I want?

Upvotes: 0

Views: 57

Answers (1)

Barmar
Barmar

Reputation: 780724

You forgot to specify that you want the smallest and largest values of the two columns, so it just picked arbitrary values from each group.

SELECT Series, MIN(YearsFrom) YearsFrom, MAX(YearsTo) YearsTo
FROM combine_data
WHERE Make = '$komb'
GROUP BY Series
ORDER BY YearsFrom

Upvotes: 3

Related Questions