CODEWITHSUNDEEP
phpmysqlsql
Peter Vorkter
Peter Vorkter

Reputation: 93

Get max and min from fields

I have two tables:

First:
id | title 
1  | aaa
2  | bbb
3  | ccc

Second:
id | first_id | one | two | three | four
1  |    1     | 3   | 1   | 4     | 6
2  |    2     | 4   | 4   | 1     | 2
3  |    3     | 1   | 2   | 3     | 4

and i would like show:

id | title | min | max 
1  | aaa   |  1  | 6
2  | bbb   |  1  | 4
3  | ccc   |  1  | 4

Is this possible with SQL? How? :)

Upvotes: 0

Views: 144

Answers (4)

John Woo
John Woo

Reputation: 263693

You can do that using UNION. try this one out:

SELECT a.id, a.title, MIN(b.c) `Min`, MAX(b.c) `Max`
FROM First a INNER JOIN 
(
    SELECT first_id, `one` c FROM `Second`
       UNION
    SELECT first_id, `two` c FROM `Second`
       UNION
    SELECT first_id, `three` c FROM `Second`
       UNION
    SELECT first_id, `four` c FROM `Second`
) b on a.id = b.First_ID
GROUP BY a.id

SEE DEMO HERE

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

select first_id,F.title ,MIN(num) [min],MAX(num) [max] from (
select first_id,[one] [num]from [Second]  union all
select first_id,[two] [num]from [Second]  union all
select first_id,[three] [num]from [Second]  union all
select first_id,[four] [num] from [Second] )[Second]
join [First] F
on Id=first_id
group by first_id,F.title

Upvotes: 1

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29975

Normalize your database. With your current setup it's not impossible but definitely not recommended.

-edit-
If you must, you can use LEAST() and GREATEST()

-edit2-

SELECT 
    a.id,
    a.title,
    LEAST(b.one,b.two,b.three,b.four) min,
    GREATEST(b.one,b.two,b.three,b.four) max
FROM first a
INNER JOIN second b ON a.id=b.first_id

Upvotes: 2

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Read Tom's answer, this would be the best to do.

Anyway, and shame on me :

SELECT f.id, f.title
 MIN(LEAST(s.one, s.two, s.three, s.four)) as min,
 MAX(GREATEST(s.one, s.two, s.three, s.four)) as max
 FROM First f
 INNER JOIN Second s on f.id = s.first_id
 GROUP BY f.id, f.title

you can remove MIN and MAX (and Group by) if Second can't have many rows with same first_id.

Upvotes: 1

Related Questions