SQL SELECT with GROUP BY clause

[Toman]

Hi

Using GROUP BY clause in SQL returns one record per group and I need it to be some extreme record in that group
For example in following table

dept      size
--------------
dept1       2
dept1       3
dept2       9
dept2       8

I'm looking for the biggest size in every group, so I want to get

dept      size
--------------
dept1       3
dept2       9

Please help with writing appropriate SQL
Tom

 

[dgrewe]

not knowing if you size is Char or Numb
SELECT distinct dept,MAX(size) FROM mydbf GROUP BY dept ORDER BY dept



David W. Grewe Dave

 

[Toman]

Thank you Dave, it works (maybe distinct not needed. I am not sure).
Trying to present things simple I've bypass the fact, that there are more fields in mydbf, something like:

dept      size      color
--------------------------
dept1       2        red 
dept1       3        blue
dept2       9        green
dept2       8        yellow

Your solution gives me those biggest sizes in groups, but how can I reach other fields in these records

dept      size      color
--------------------------
dept1       3        blue
dept2       9        green

Will you help.
Tom.

 

[dgrewe]

SELECT distinct dept , MAX(size) , otherfield1 , otherfield2,..... FROM mydbf GROUP BY dept ORDER BY dept

David W. Grewe Dave

 

[Toman]

Dave, I'm afraid it's not so easy. Try to run following program and you will see that in group dept2 is correctly shown max size 9. Color for size 9 is green, but SQL returns yellow.

CREATE table tblMyDbf (dept c(10),size n(2),color c(10))
   INSERT INTO tblMyDbf VALUES ("dept1",2,"red")
   INSERT INTO tblMyDbf VALUES ("dept1",3,"blue")
   INSERT INTO tblMyDbf VALUES ("dept2",9,"green")
   INSERT INTO tblMyDbf VALUES ("dept2",8,"yellow")
SELECT distinct dept , MAX(size) , color FROM tblMyDbf GROUP BY dept ORDER BY dept

Any idea?
Tom.

 

[Mike Lewis]

Tom,

Color for size 9 is green, but SQL returns yellow.

That's not surprising. How would VFP know that you want to match the colour against the size? In fact, in VFP 8.0 and later, your SELECT would generate an error - and rightly so.

Here's a possible solution:

SELECT distinct dept , MAX(size) AS MaxSize ;
  FROM tblMyDbf GROUP BY dept ;
  ORDER BY dept INTO CURSOR Temp

SELECT Temp.Dept, temp.MaxSize, tbl.Color ;
  FROM Temp ;
  JOIN tblMyDBF tbl ON tbl.Dept = temp.dept ; 
  AND tbl.size = temp.MaxSize

In fact, you could probably combine those into one statement and avoid the temporary cursor, but I just wanted to show you a simple way of achieving your goal.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[craigber]

Dave,

There are additional issues with the solution you posted. Grouping needs to be done on all non-aggregate fields to avoid results that could be incorrect. The SELECT statement was changed in VFP 8.0 to give an error if all the fields weren't included.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[dgrewe]

Mike, Craig,
Yep Your right,
I wrote the SELECT for his first question.

Did not think it out when he added the additional info on the problem.

I just love it when people ask you to help them and then only tell you part of the story. I normally tune them out after that.



David W. Grewe Dave

 

[Toman]

Thank you very much gentleman. My problem has been solved.
Tom.