2

I am expecting the following to yield more than 3 rows, since there are ties of min(a.[Sepal.Width]) within each of a.[Species], however only 3 rows are returned:

sqldf(' select a.[Species], min( a.[Petal.Width]) from iris a group by a.[Species] ')
     Species min( a.[Petal.Width])
1     setosa                   0.1
2 versicolor                   1.0
3  virginica                   1.4

  • 1: Is this normal behavior of SQLite?

  • 2: How do I design a select string to also return the ties? (preferably as close to the original search string as possible)

Edit: I see now that my confusion stemmed from being used to using SAS proc sql, which would return all matching rows. I have now learned that this is indeed peculiar to SAS proc SQL and not to be expected from other SQL incarnations. People with the same SAS-induced confusion as me will benefit from this answer which articulate this SAS behavior: https://stackoverflow.com/a/25539916/1831980

Community
  • 1
  • 1
Rasmus Larsen
  • 5,721
  • 8
  • 47
  • 79
  • 2
    You are confused. I am confused. What would the value of the *maximum* have to do with a query on the *minimum*? And, a `GROUP BY` query returns one row per group. Period. One row per group. – Gordon Linoff Feb 22 '17 at 20:52
  • 1
    Yes, with `group by` it will return one row for each group. – G. Grothendieck Feb 22 '17 at 21:15

2 Answers2

2

This is how I would do it:

sqldf::sqldf('SELECT b.[Species], b.[Petal.Width] FROM iris b JOIN
             (SELECT [Species], MIN( [Petal.Width]) AS [Petal.Width] 
              FROM iris GROUP BY [Species]) a 
             USING ([Species],[Petal.Width])')
HubertL
  • 19,246
  • 3
  • 32
  • 51
1

I suspect this is what you want:

select i.*
from iris i
where i.sepal_width = (select max(i2.sepal_width)
                       from iris i2
                       where i2.species = i.species
                      );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786