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
