Re: [sqlite] Update one table from matches in another
update locations set ItemCount='1' where exists( select Location from hive where locations.Location=hive.Location ) Okay, seems that I wasn't too far off. Thank you very much for the help, I certainly needed it:):) update locations set ItemCount='0' where NOT exists( select Location from hive where locations.Location=hive.Location ) I must be having a brain fade day, was sure that I had tried this. Ah well, living and learning as much as I can. Thanks once again. -- View this message in context: http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32233295.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update one table from matches in another
You can do both in one pass: update locations set ItemCount = case when Location in (select Location from hive) then '1' else '0' end; Thanks for your help Igor, extremely elegant solution. -- View this message in context: http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32233560.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Search multiple columns AND extra coparator
Hello folks, sorry to bother you. You must admit I take 6 months or more to ask questions:):) Using the first part of this query works fine to return data by searching the multiple concatenated columns, very happy with that. SELECT * FROM sitelist WHERE sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes LIKE '%wik%' 7 But I also want to AND the following columns to apply to the results and I am not getting it (So what's new, I hear you say??) AND category='Bundled Software' AND owner='Gary' ORDER BY sitename So I want the columns to be searched for a match (working) but ONLY DISPLAYED if the category and owner are matching as well (Not working). Have tried all manner of ANDs and ORs and WHEREes in various ways and am not getting it. Would anyone be able to elucidate? -- View this message in context: http://old.nabble.com/Search-multiple-columns-AND-extra-coparator-tp28356196p28356196.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search multiple columns AND extra coparator
Just add your AND and ORDER by clauses on after the '%wik%'. But I don't know why there's a '7' at the end of your original line. I don't think it does anything. Perhaps a typo when you made the post ? SELECT * FROM sitelist WHERE sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes LIKE '%wik%' AND category='Bundled Software' AND owner='Gary' ORDER BY sitename Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and it didn't work. I know there is a limit to field concatenation this way but I don't know what it is so will have to play as it is not giving me any results at the moment. Oh well, thanks for the idea...I don't really need any sleep tonight..:):) -- View this message in context: http://old.nabble.com/Search-multiple-columns-AND-extra-comparator-tp28356196p28356587.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search multiple columns AND extra coparator
Igor Tandetnik wrote: Define didn't work. What results did this query return, and how do these results differ from your expectations? it is not giving me any results at the moment. Well, do you actually have any records that match all the conditions? Show a sample of your data; in particular, show the record that you believe should be returned by the query. -- Igor Tandetnik Thanks Igor but as I indicated to Simon, I finally figured it out and pasted the resulting query string to Simon. Now I may fall out of this chair and die quietly. P.s. Your help in the past has also been invaluable, thank you. -- View this message in context: http://old.nabble.com/Search-multiple-columns-AND-extra-comparator-tp28356196p28356875.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite
To to get columns that begin with a number range or any number, I would use in SQL server: SELECT * FROM mytable WHERE myfield LIKE '[0-9]%' This obviously doesn't work in sqlite and I have been searching for an equivalence. -- View this message in context: http://old.nabble.com/Searching-for-a-LIKE-%27-0-9--%27-equivalence-in-sqlite-tp27412013p27412013.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite
flakpit wrote: To to get columns that begin with a number range or any number, I would use in SQL server: SELECT * FROM mytable WHERE myfield LIKE '[0-9]%' This obviously doesn't work in sqlite and I have been searching for an equivalence. Thank you for your kind assistance folks, brilliant! -- View this message in context: http://old.nabble.com/Searching-for-a-LIKE-%27-0-9--%27-equivalence-in-sqlite-tp27412013p27412279.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slim down join results (all fields returned)
You did. You should still do some SQL tutorials. In the meantime, I hope the above helps. Adjusted the fieldnames accordingly and your example worked perfectly. Thank you. However, repeating that I should do some SQL tutorials is annoying in extremis. I had been to many SQL tutorials and always failed to understand more complex examples until I had a concrete, real world example to work from. That is why I came here for help as the examples you people provide are far better a base for me to comprehend than the relativity of a tutorial. I remember several examples from other people here that helped me get along far better than a 'relative' tutorial. Thanks for the help, the query below with slightly adjusted field names works perfectly. Now I am going to go and get tortured by my wife, beaten up by my daughter and whinged at by the cat. Looks like no more struggling till nightfall! SELECT pubs.*, notes.note_note, publishers.publisher_name FROM pubs JOIN notes ON pubs.note_id = notes.note_id JOIN publishers ON pubs.publisher_id = publishers.publisher_id WHERE pubs.pub_title LIKE '%salem%' -- View this message in context: http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23110028.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slim down join results (all fields returned)
On 18/04/2009 2:33 PM, flakpit wrote: If anyone else has a moment and a concrete example instead of sending me to tutorials, feel free to step in here. If you would take a moment to read the fraction of a screen of a tutorial that I pointed you at (and which contains a concrete example), instead of repeating what you have already explained you want and is screamingly obvious anyway, you might actually *learn* something, and be able to find answers for yourself quickly. Do me a favour and stick your condescending arrogance where the light doesn't shine. As I get older and slower, I have trouble working things out for myself which is why I asked for help here and I don't need to be told to go read tutorials as I have repeatedly told you that I don't necessarily understand but it seems you have trouble reading English as well as basic courtesy and manners. Since you have trouble using the few brain cells you posess for basic courtesy and understanding that not all of us older folks can work things out as fast as you do and it isn't screamingly obvious anyway to some of us, don't feel that you have to demean yourself any more by helping. I certainly don't need your help with that blinding arrogance. -- View this message in context: http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23110073.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slim down join results (all fields returned)
Currently, I return any needed data like this. select * from pubs,notes,publishers where pub_title like '%salem%' and pubs.note_id=notes.note_id and pubs.publisher_id=publishers.publisher_id And it works except for all fields in the matching tables being returned. Is there any way using joins (been playing but can't get them working) so that a statement like pubs.note_id=notes.note_id Would return only the contents of the notes.note_note field and not all the fields in the notes table? -- View this message in context: http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23098746.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slim down join results (all fields returned)
SELECT pubs.* from pubs, notes, publishers WHERE ... Thanks, but it didn't work the way I expected it to:) Now I have my query laid out a lot better and can 'slim' it down from here SELECT * FROM pubs INNER JOIN notes ON pubs.note_id=notes.note_id ; notes. INNER JOIN publishers ON pubs.publisher_id=publishers.publisher_id WHERE pub_title LIKE '%salem%' How do I return only the matching notes.note_notes field in that join and no the rest of the fields in notes? How do I return only the matching publishers.publisher_name field in that join and not the rest of the fields in publishers? Probably still explaining myself very badly here. Oh well. -- View this message in context: http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23107065.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slim down join results (all fields returned)
If anyone else has a moment and a concrete example instead of sending me to tutorials, feel free to step in here. SELECT * FROM pubs; I want ALL columns from the 'pubs' table WHERE pub_title LIKE '%salem%' ; Where the title sounds like 'salem' In the pubs table, there is an ID field that points to the note in the 'notes' table. I want to retrieve that note for the currently retrieved record from the 'pubs' table and no other column in the 'notes table' In the pubs table, there is an ID field that points to the publisher in the 'publisher' table. I want to retrieve that publisher for the currently retrieved record from the 'publishers' table and no other column in the 'publishers' table The query below retrieves the correct note and publisher for each retrieved record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every other column in 'notes' and 'publishers' which I don't want. I just want the matching notes.note_note for each pubs record and not notes.id or notes.idx etc. I just want the matching publishers.publisher_name for each pubs record and not publishers.id or publishers.idx etc. SELECT * FROM pubs INNER JOIN notes ON pubs.note_id=notes.note_id INNER JOIN publishers ON pubs.publisher_id=publishers.publisher_id WHERE pub_title LIKE '%salem%' I believe I explained myself well enough this time. -- View this message in context: http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23109313.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fetch first non-sequential record
Good evening. Due to many deletions, my database is non sequential anymore. Is there any way to fetch the very first record in the database and find the id number? Or for that matter, a function to select the last record? I've tried the SELECT TOP 1 function butt that apparently isn't sqlite syntax. -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21125757.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fetch first non-sequential record
Dan Kennedy-4 wrote: Is there any way to fetch the very first record in the database and find the id number? The 'first record' is a malleable concept. You can find the record with the lowest rowid value with: SELECT ... FROM table ORDER BY rowid LIMIT 1; Thank you, that worked. Two questions spring to mind however.. 1. How many records are processed by SQLITE to get you the lowest rowid value this way, does it have any significant impact on memory? 2. The above statement gets the lowest rowid but there appears to be no implicit statement to the direction of the ORDER BY clause. Can the statement be reversed to get the highest rowid? Thanks for all your help. -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21126226.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fetch first non-sequential record
2. The above statement gets the lowest rowid but there appears to be no implicit statement to the direction of the ORDER BY clause. Can the statement be reversed to get the highest rowid? SELECT ... FROM table ORDER BY rowid DESC LIMIT 1; Okay, I feel very stupid now, hadn't twigged. I was typing in DESCENDING rather than DESC and didn't know why it was failing (LOL) I think I will go to bed now. -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21126513.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_prepare/bind to exnter data or just query?
I've been escaping single quote characters in all my text fields and using the sqlite_execute function to put the data into the table. But is it possible to use the prepare/bind commands to enter data so that I don't have to do this? I've been using the column_text type to get back a block of text and it's being truncated at the first CR/LF pair in the text block and I can't help thinking that I could have avoided this somehow by entering it with a prepared statement. Or am I completely wrong? -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18977257.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
SQLite doesn't truncate anything. Either you truncated at the time you put the data into the database in the first place, or you are truncating now when looking at the string. It definately wasn't truncated when I put it in, I checked. So as you say, something is truncating it as it is being read out somewhere. I'm just peeking the text in the column_text returned data. -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982143.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Peter Holmes-4 wrote: Yep. Works great! For example: sqlite3_prepare_v2(db,INSERT INTO ans VALUES (?,?);,-1,stmt,NULL); sqlite3_bind_text(stmt,0,Peter's,-1,SQLITE_STATIC); sqlite3_bind_text(stmt,1,Reply,-1,SQLITE_STATIC); sqlite3_step(stmt); sqlite3_reset(stmt); so ?,? represent columns to be filled in right? and stmt,0 is column 0 and stmt,1 is column 1? and the -1 is add to end of db? -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982253.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Enrique Ramirez-3 wrote: Depends on where you're looking at your block of text. Are you using a GUI SQLite Manager of sorts, or maybe peeking at the variable's contents from a dev IDE? I checked the contents of the db to ensure that all my text was in it and it was. Then I peek the results of a column_text data return and it is truncated at the first CR/LF pair. Wish I knew why. There are no nulls in the data, checked with a hex editor. Maybe there is a problem with my dev IDE's peek function. Hmmm. -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982354.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Dennis Cote wrote: See, http://www.sqlite.org/c3ref/prepare.html for details on prepare and previous link for detail on bind_text. Dennis Cote Thank you for the links Dennis, this will help me a lot. -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18983578.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
I understand your solution Woody, but it isn't practical for me, not for my home shopping list program. A full on relational database like that is an awful lot of work and it's only for home use (and any other family I can con into using it and testing it). I'd go for the relational route if I were designing an enterprise wide product, it's only sensible (as you intimated) but Igor's solution (that I also found independantly) will work fine for a small system for now. Thanks for the ideas, will keep proper design in mind if I ever get talked into doing something for a company (something I try mightily to avoid, believe me!!!) -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230807.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
Harold Wood amp; Meyuni Gani wrote: U, hmm. The tips I gave you were from my pda based shopping program that will be selling for 9.99 soon. Good on you Woody, hope you sell a lot.! If my eyes were up to the challenge of reading my PDA's small screen, i'd buy a copy and save myself work (LOL!) With the amount of junk I've churned out of the years, i've yet to get anyone to buy anything yet (ROFL). Actually, not quite true. One registration out of 6,500 downloads of my address book. -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18231100.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
Thank you Igor, the solution below (that you also posted) is what I found in
the forums after hours of searching when I should have been asleep. And
rather that produce shoddy code, I downloaded a proper sqlite tool to verify
that it was all working fine. Thank you for the response, I have learned a
lot from you recently:)
create table t(a, b, unique(a, b));
insert into t values('a', 'b'); -- ok
insert into t values('a', 'c'); -- ok
insert into t values('z', 'b'); -- ok
insert into t values('z', 'b'); -- fails: this pair already exists
--
View this message in context:
http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230769.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple constraints per table?
This is the way I normally create my shopping database, but this leads to thousands of duplicates. CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER PRIMARY KEY AUTOINCREMENT) 'item' is the full retailer's description for this shopping item. If I make this unique, then i eliminate all duplicates and any further entries of this item, so that's no good. item TEXT CONSTRAINT item UNIQUE is it legal sql syntax to allow more than one constraint field in table creation? I need at least these four below to guarantee that duplicate items do make it into the database but not on the same day. item TEXT CONSTRAINT item UNIQUE units TEXT CONSTRAINT units UNIQUE shop TEXT CONSTRAINT shop UNIQUE date TEXT CONSTRAINT date UNIQUE So the below three records would be allowed as the unit weight is different and also there are two different dates and as everyone knows, peppers come in all shapes and sizes (grin) (This is okay) pepper, 120gm, coles, 02/02/2006 pepper, 50gm, coles, 02/02/2006 pepper, 50gm, coles, 04/11/2007 Or would the multiple constraints work globally (if legal syntax) Would it end up with only the single record below because the all constraints operated globally? pepper, 120gm, coles, 02/02/2006 -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18209309.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Updating a BLOB field
I use a language called PureBasic and am playing with BLOB data. Got data saving successfully but read in these forums that updating is the same. Yes, I know it's a compiled statement, saving to database is fine, I can even view my saved blobs so I know my code works.. The SQL to insert the blob into my database is : INSERT INTO archive (docnumber, document) VALUES (?,?) How to change the syntax to update a blob field? Is it the same as updating normal data or a variant on the above? -- View this message in context: http://www.nabble.com/Updating-a-BLOB-field-tp18008842p18008842.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Extracting distinct category and subcategory pairs
Hello, due to the wonderful help that I have recieved before, I dare to ask I have records in a database with Category and Subcategory fields. How would I formulate the query to return distinct results and return for whatever I need to do with them? There may be thousands of category and subcategory duplications as they are not the primary record identifier. I.e. A record has a category of Manuals and a subcategory Audio Another record has a category of Manuals and a subcategory Phone A record has a category of Receipts and a subcategory Water A record has a category of Receipts and a subcategory Gas A record has a category of Receipts and a subcategory Electricityr So they would look like: Manuals + Audio + Phone Receipts + Water + Gas + Electricity The below doesn't work as I am unfamiliar with these sorts of queries. SELECT DISTINCT category from documents [SELECT DISTINCT IN subcategory FROM documents] -- View this message in context: http://www.nabble.com/Extracting-distinct-category-and-subcategory-pairs-tp17734716p17734716.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Extracting distinct category and subcategory pairs
Dennis Cote wrote: flakpit wrote: You could try select distinct category, subcategory from documents order by category, subcategory; Then your application can do the display formatting, such as suppressing the display of the category when it is the same as the last record. You might be right at that. I'll have a try. Thank you for the suggestion. -- View this message in context: http://www.nabble.com/Extracting-distinct-category-and-subcategory-pairs-tp17734716p17735884.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Listing duplicate entries
Is there a way of querying the database to list all duplicate entries from a column in the same table? Something like SELECT * FROM mytable WHERE last NOT UNIQUE fred, johnson roger, johnson -- View this message in context: http://www.nabble.com/Listing-duplicate-entries-tp16941525p16941525.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
Igor Tandetnik wrote: flakpit [EMAIL PROTECTED] wrote: Is there a way of querying the database to list all duplicate entries from a column in the same table? Something like SELECT * FROM mytable WHERE last NOT UNIQUE select * from mytable t1 where exists ( select * from mytable t2 where t1.last=t2.last and t1.rowid != t2.rowid); -- or select * from mytable where rowid not in ( select rowid from mytable group by last having count(*) = 1 ); Igor Tandetnik Thank you!!! I will have fun trying this out. -- View this message in context: http://www.nabble.com/Listing-duplicate-entries-tp16941525p16951811.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Go to specific row in database
Apologies for not being too clear. Normally, I'd just query a database based on where criteria to get results, but that is based on a continually expanding and dynamically changing database. In this case, I have a static (book records) database and I want to select a specific row based on the premise that I might have omitted id numbers so I cannot query the records that way. MySQL and msSql can seek to a specific row (line number in a database) and this must be based on some sort of database API that I am unfamiliar with and I wondered to know if there was a generic sql statement way of doing this that would work with sqlite or indeed, any database engine. To get the row number, you just: select rowid from MyTable where Condition; select * from addresses where rowid=1 did the trick for me. Thank you so much for the help. Hope I can help back some day -- View this message in context: http://www.nabble.com/Go-to-specific-row-in-database-tp16367156p16367317.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Go to specific row in database
MySql and msSql have seek commands to go to a( or return) a specific row in a database base don the internal row number rather than an id or other identifier. Is there a generic SELECT statement that can do this for sqlite or indeed, any other sql variant? Regards. -- View this message in context: http://www.nabble.com/Go-to-specific-row-in-database-tp16367156p16367156.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
