Re: [sqlite] locating a minimum in SQLite 2

[James K. Lowden]

On Thu, 1 Dec 2016 11:12:48 -0800
James Walker  wrote:

> SELECT MIN(PRICE), IDENT FROM INFO;
> 
> and get what I want.  But in SQLite 2 (legacy code), this doesn't 
> work... I get the minimum value, but NULL in the IDENT column.  

Does it work with both versions if you write it correctly?  

SELECT MIN(PRICE), IDENT 
FROM INFO
GROUP BY IDENT;

> I want to find the IDENT of the row with the minimum value of PRICE

That's what quantification was invented for

select * from info 
where price in (
SELECT MIN(PRICE)
FROM INFO
);

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

Re: [sqlite] locating a minimum in SQLite 2

[James Walker]

On 12/1/2016 11:55 AM, Baruch Burstein wrote:

On Thu, Dec 1, 2016 at 9:12 PM, James Walker 
wrote:


Let's say I have a table INFO with columns PRICE and IDENT, and I want to
find the IDENT of the row with the minimum value of PRICE.  In SQLite 3, I
can say

SELECT MIN(PRICE), IDENT FROM INFO;

and get what I want.  But in SQLite 2 (legacy code), this doesn't work...
I get the minimum value, but NULL in the IDENT column.  I could say

SELECT PRICE, IDENT FROM INFO ORDER BY PRICE;

and ignore all but the first row of the result, but I'm sure there must be
a better way?



LIMIT 1?


Yes, LIMIT 1 does it. I knew there had to be a simple way.  Thanks.

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

Re: [sqlite] locating a minimum in SQLite 2

[John McKown]

On Thu, Dec 1, 2016 at 1:12 PM, James Walker 
wrote:

> Let's say I have a table INFO with columns PRICE and IDENT, and I want to
> find the IDENT of the row with the minimum value of PRICE.  In SQLite 3, I
> can say
>
> SELECT MIN(PRICE), IDENT FROM INFO;
>
> and get what I want.  But in SQLite 2 (legacy code), this doesn't work...
> I get the minimum value, but NULL in the IDENT column.  I could say
>
> SELECT PRICE, IDENT FROM INFO ORDER BY PRICE;
>
> and ignore all but the first row of the result, but I'm sure there must be
> a better way?
>
>
​Well, standard SQL seems to work, but would return multiple lines if
multiple rows have the minimal price, but you could use LIMIT 1:

​SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table info(price int, ident text);
sqlite> insert into info(price,ident) values(1,'a1');
sqlite> insert into info(price,ident) values(2,'b');
sqlite> insert into info(price,ident) values(3,'c');
sqlite> insert into info(price,ident) values(1,'a');
sqlite> select price, ident from info where price=(select min(price) from
info);
1|a1
1|a
sqlite> select price, ident from info where price=(select min(price) from
info) limit 1;
1|a1
sqlite>

​

​In sqlite 2 the same works:

SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table info(ident text,price int);
sqlite> insert into table(ident, price) values('a',1);
SQL error: near "table": syntax error
sqlite> insert into info(ident, price) values('a',1);
sqlite> insert into info(ident, price) values('a1',1);
sqlite> insert into info(ident, price) values('b',2);
sqlite> insert into info(ident, price) values('c',3);
sqlite> select min(price), ident from info;
1|
sqlite> select price, ident from info where price=(select min(price) from
info);
1|a
1|a1
sqlite> select price, ident from info where price=(select min(price) from
info) limit 1;
1|a
sqlite>
​

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locating a minimum in SQLite 2

[Baruch Burstein]

On Thu, Dec 1, 2016 at 9:12 PM, James Walker 
wrote:

> Let's say I have a table INFO with columns PRICE and IDENT, and I want to
> find the IDENT of the row with the minimum value of PRICE.  In SQLite 3, I
> can say
>
> SELECT MIN(PRICE), IDENT FROM INFO;
>
> and get what I want.  But in SQLite 2 (legacy code), this doesn't work...
> I get the minimum value, but NULL in the IDENT column.  I could say
>
> SELECT PRICE, IDENT FROM INFO ORDER BY PRICE;
>
> and ignore all but the first row of the result, but I'm sure there must be
> a better way?


LIMIT 1?

˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] locating a minimum in SQLite 2

[James Walker]

Let's say I have a table INFO with columns PRICE and IDENT, and I want 
to find the IDENT of the row with the minimum value of PRICE.  In SQLite 
3, I can say


SELECT MIN(PRICE), IDENT FROM INFO;

and get what I want.  But in SQLite 2 (legacy code), this doesn't 
work... I get the minimum value, but NULL in the IDENT column.  I could say


SELECT PRICE, IDENT FROM INFO ORDER BY PRICE;

and ignore all but the first row of the result, but I'm sure there must 
be a better way?

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