SQL MAX and MIN in one column

Question

I have a problem to get the max and the min value, I want the result to be XXL and XXS

  SELECT MAX(tblSizeXL.SizeXLName) AS maxSize, 
         MIN(tblSizeXL.SizeXLName) AS minSize    
    FROM Product 
    JOIN tblSizeXL ON Product.SizeXLID = tblSizeXL.SizeXLID
   WHERE (Product.GroupID = @GroupID)
GROUP BY tblSizeXL.SizeXLID
ORDER BY tblSizeXL.SizeXLID DESC

tblSize

SizeXLID     SizeXLName
-----------------------
1            XXS
2            XS
3            S
4            M
5            L
6            XL
7            XXL
8            XXXL

What RDBMS? Also, wouldn't the max size be XXXL? And is tblSizeXL the same as tblSize? — LittleBobbyTables - Au Revoir, Sep 29 '10 at 15:16
Yes its SQL-server, if I have max SizeXLID how do I get the name as result instead of the MAX number. — Nicklas, Sep 29 '10 at 15:52
Are we guaranteed that SizeXLID will always order the sizes in the correct order? For example, you'd never add another row to tblSize at a later time for (9, 'XXXS')? — Joe Stefanelli, Sep 29 '10 at 16:09
hmm maybe its better to add XXXXXXS from the begining, that dosent bother to much, thanks — Nicklas, Sep 29 '10 at 16:15
@Nicklas, it might be better to add a size_order column to tblSizeXL and select MAX and MIN of size_order, rather than relying on the ID column always being in the correct order. That way, you can add new sizes out of sort sequence. — , Sep 29 '10 at 16:51

score 1 · Accepted Answer · answered Sep 29 '10 at 16:03

1

You're going to have to do an inner queries to get the data you're looking for:

SELECT max.SizeXLName as maxSize, min.SizeXLName as minSize
  FROM 
  (SELECT MAX(tblSizeXL.SizeXLID) as MaxSizeXLID, MIN(tblSizeXL.SizeXLID) as MinSizeXLID
     FROM Product
     JOIN tblSizeXL ON Product.SizeXLID = tblSizeXL.SizeXLID
    WHERE Product..GroupID = @GroupID) base
  JOIN tblSizeXL max ON max.SizeXLID = base.MaxSizeXLID
  JOIN tblSizeXL min on min.SizeXLID = base.MinSizeXLID

answered Sep 29 '10 at 16:03

Jeff Wight

833
5
11

Looks better, but the result is wrong, I get XXXL and XS, so its almost :) – Nicklas Sep 29 '10 at 16:13
My fault, there is an XXS but there was no Product that had that size. Great thanks alot! – Nicklas Sep 29 '10 at 16:57

score 0 · Answer 2 · answered Sep 29 '10 at 15:16

0

Remove the group by clause and your query should be fine.

answered Sep 29 '10 at 15:16

CoderSivu

141
2
7

Then I get this: Column "tblSizeXL.SizeXLID" is invalid in the ORDER Y clause because it is not contained in either an aggregate function or the GROUP BY clause. – Nicklas Sep 29 '10 at 15:42
If I understood your question, you expect the result to be a single row. In that case you don't need the 'order by' clause – CoderSivu Sep 30 '10 at 07:44

SQL MAX and MIN in one column

tblSize

2 Answers2