Re: [sqlite] sqlite select max(field1), field2 from table1

[Simon Slavin]

On 12 Sep 2012, at 1:03pm, Richard Hipp  wrote:

> On Wed, Sep 12, 2012 at 7:51 AM, Tim Streater  wrote:
> 
>> On 12 Sep 2012 at 11:37, Richard Hipp  wrote:
>> 
>>> On Wed, Sep 12, 2012 at 6:15 AM, Bart Smissaert
>>> wrote:
>>> 
 [snip] max(field1) [snip]
>> 
>> Is there any means of influencing which record would be returned in this
>> circumstance, such as by an ORDER BY?
> 
> No.  If you have multiple rows with the same maximum value, then the one
> that ends up being returned is arbitrary.

In other words, there's no way to do what you want in a single SELECT.  You can 
do one SELECT just to find the value for max(field1), and then another SELECT 
which uses ORDER BY to choose one of the records with that value for field1.  I 
think you might even be able to do it using a sub-SELECT:

SELECT field2, field3 FROM myTable
WHERE field1 = (SELECT max(field1) FROM myTable)
ORDER BY acquisitionDate DESC
LIMIT 1

I may have the above syntax wrong since I don't use sub-selects myself.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite select max(field1), field2 from table1

[Richard Hipp]

On Wed, Sep 12, 2012 at 7:51 AM, Tim Streater  wrote:

> On 12 Sep 2012 at 11:37, Richard Hipp  wrote:
>
> > On Wed, Sep 12, 2012 at 6:15 AM, Bart Smissaert
> > wrote:
> >
> >> Had a look at the new option as in the SQL above.
> >> Noticed it will only return one record, even if there are more records
> >> where
> >> field1 equals max(field1).
> >> I suppose it returns the first record it finds where field1 =
> max(field1).
> >> Is this indeed how it works?
>
> > Yes.
>
> Is there any means of influencing which record would be returned in this
> circumstance, such as by an ORDER BY?
>

No.  If you have multiple rows with the same maximum value, then the one
that ends up being returned is arbitrary.


>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite select max(field1), field2 from table1

[Tim Streater]

On 12 Sep 2012 at 11:37, Richard Hipp  wrote: 

> On Wed, Sep 12, 2012 at 6:15 AM, Bart Smissaert
> wrote:
>
>> Had a look at the new option as in the SQL above.
>> Noticed it will only return one record, even if there are more records
>> where
>> field1 equals max(field1).
>> I suppose it returns the first record it finds where field1 = max(field1).
>> Is this indeed how it works?

> Yes.

Is there any means of influencing which record would be returned in this 
circumstance, such as by an ORDER BY? Or does that merely order the (one) 
returned record :-)

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users