Re: [sqlite] Use of two columns for a key and query on first clm.
order by id is not guaranteed. use 'order by id' and it will. Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Neville Franks Sent: Thursday, March 20, 2008 8:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Use of two columns for a key and query on first clm. Let me rephrase the question slightly. If I do select * from table where clm1='def'; then step through the results will I see all rows that include 'def'. The answer must be yes. The only issue is what order they will be in. If I want them ordered by clm2 then yes I'd probably need use ORDER BY. However in this specific example I would have thought the index order would be used, which is clm1+clm2 which is the same as using ORDER BY clm2. But I appreciate this isn't guaranteed. Friday, March 21, 2008, 11:41:52 AM, you wrote: JS> No, use ORDER BY JS> Neville Franks wrote: >> If I use two columns for a key (primary or separate index) and query >> just on the first column component will I always get back the first >> match in a set. For example. >> >> - >> create table mytable ( clm1 text collate nocase, clm2 text >> collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); >> >> insert following: >> Clm1 Clm2 >> abc 123 >> abc 456 >> abc 789 >> def 123 >> def 456 >> def 789 >> >> select * from table where clm1='def'; >> - >> >> Will the returned row always be def - 123. ie. the first row for def? >> >> I've looked at the query plan for this select and it does use the >> index if clm1 alone is in the query and it appears to match on the >> first row. >> >> Also my tests indicate I do get back the first matching row. But I'd >> like confirmation if possible. >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of two columns for a key and query on first clm.
Let me rephrase the question slightly. If I do select * from table where clm1='def'; then step through the results will I see all rows that include 'def'. The answer must be yes. The only issue is what order they will be in. If I want them ordered by clm2 then yes I'd probably need use ORDER BY. However in this specific example I would have thought the index order would be used, which is clm1+clm2 which is the same as using ORDER BY clm2. But I appreciate this isn't guaranteed. Friday, March 21, 2008, 11:41:52 AM, you wrote: JS> No, use ORDER BY JS> Neville Franks wrote: >> If I use two columns for a key (primary or separate index) and query >> just on the first column component will I always get back the first >> match in a set. For example. >> >> - >> create table mytable ( clm1 text collate nocase, clm2 text >> collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); >> >> insert following: >> Clm1 Clm2 >> abc 123 >> abc 456 >> abc 789 >> def 123 >> def 456 >> def 789 >> >> select * from table where clm1='def'; >> - >> >> Will the returned row always be def - 123. ie. the first row for def? >> >> I've looked at the query plan for this select and it does use the >> index if clm1 alone is in the query and it appears to match on the >> first row. >> >> Also my tests indicate I do get back the first matching row. But I'd >> like confirmation if possible. >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of two columns for a key and query on first clm.
No, use ORDER BY Neville Franks wrote: > If I use two columns for a key (primary or separate index) and query > just on the first column component will I always get back the first > match in a set. For example. > > - > create table mytable ( clm1 text collate nocase, clm2 text collate nocase, > constraint mycs1 primary key( clm1, clm2 ) ); > > insert following: > Clm1 Clm2 > abc 123 > abc 456 > abc 789 > def 123 > def 456 > def 789 > > select * from table where clm1='def'; > - > > Will the returned row always be def - 123. ie. the first row for def? > > I've looked at the query plan for this select and it does use the > index if clm1 alone is in the query and it appears to match on the > first row. > > Also my tests indicate I do get back the first matching row. But I'd > like confirmation if possible. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of two columns for a key and query on first clm.
If I use two columns for a key (primary or separate index) and query just on the first column component will I always get back the first match in a set. For example. - create table mytable ( clm1 text collate nocase, clm2 text collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); insert following: Clm1 Clm2 abc 123 abc 456 abc 789 def 123 def 456 def 789 select * from table where clm1='def'; - Will the returned row always be def - 123. ie. the first row for def? I've looked at the query plan for this select and it does use the index if clm1 alone is in the query and it appears to match on the first row. Also my tests indicate I do get back the first matching row. But I'd like confirmation if possible. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
