query

What's the SQL query to list all rows that have 2 column sub-rows as duplicates?

Ok I have a table that has redundant data and I'm trying to identify all rows that have duplicate sub-rows (for lack of a better word). By sub-rows I mean considering COL1 and COL2 only. So let's say I have something like this: COL1 COL2 COL3 --------------------- aa 111 blah_x aa 111 blah_j aa 112 blah_m...

How do you execute a dynamic SQL Query against MS Access 2003 via VBA?

Hello Everyone, This is a super basic question but I'm trying to execute a Query that I'm building via some form values against the MS Access database the form resides in. I don't think I need to go through ADO formally, but maybe I do. Anyway, some help would be appreciated. Sorry for being a n00b. ;) ...

Problem joining on the highest value in mysql table

I have a products table... and a revisions table, which is supposed to track changes to product info I try to query the database for all products, with their most recent revision... select * from `products` as `p` left join `revisions` as `r` on `r`.`product_id` = `p`.`product_id` group by `p`.`product_id` order by `r`.`modified` ...

Is there any thing such as SELECT LAST in sql query?

I am using sybase database to query the daily transaction report. I had subquery within my script. Here as it goes: SELECT orders.accountid ,items.x,etc (SELECT charges.mistotal FROM charges where items.id = charges.id) FROM items,orders WHERE date = '2008-10-02' Here I am getting the error message as Subquery cannot return more than...

Does this query look optimized?

I'm writing a query for an application that needs to list all the products with the number of times they have been purchased. I came up with this and it works, but I am not too sure how optimized it is. My SQL is really rusty due to my heavy usage of ORM's, But in this case a query is a much more elegant solution. Can you spot anything...

SQL Server Query Question: If I stop a long running query, does it rollback?

A query that is used to loop through 17 millions records to remove duplicates has been running now for about 16 hours and I wanted to know if the query is stopped right now if it will finalize the delete statements or if it has been deleting while running this query? Indeed, if I do stop it, does it finalize the deletes or rolls back? ...

NHibernate Query problem

I'm quite new to NHibernate and starting to find my way around. I have a domain model that is somewhat like a tree. Funds have Periods have Selections have Audits Now I would like to get all Audits for a specific Fund Would look like this if I made it in SQL SELECT A.* FROM Audit A JOIN Selection S ON A.fkSelectionID = S.pkID JOIN Pe...

MySQL Query: LIMITing a JOIN

Say I have two tables I want to join. Categories: id name ---------- 1 Cars 2 Games 3 Pencils And items: id categoryid itemname --------------------------- 1 1 Ford 2 1 BMW 3 1 VW 4 2 Tetris 5 2 Pong 6 3 Foobar Pencil Factory I want a...

What am I doing wrong when using RAND() in MS SQL Server 2005?

I'm trying to select a random 10% sampling from a small table. I thought I'd just use the RAND() function and select those rows where the random number is less than 0.10: SELECT * FROM SomeTable WHERE SomeColumn='SomeCondition' AND RAND() < 0.10 But I soon discovered that RAND() always returns the same number! Reminds me of this xkc...

SQL query for cumulative frequency of list of datetimes

I have a list of times in a database column (representing visits to a website). I need to group them in intervals and then get a 'cumulative frequency' table of those dates. For instance I might have: 9:01 9:04 9:11 9:13 9:22 9:24 9:28 and i want to convert that into 9:05 - 2 9:15 - 4 9:25 - 6 9:30 - 7 How can I do that? Can i ev...

What LogParser UI do you use?

I just started using Log Parser Lizard to examine my IIS and Event logs. What UI tool do you use on top of LogParser 2.2 to view your log files on production? ...

Safe to Run LogParser Against Live Production IIS Log?

Is it safe to run LogParser against our live production IIS log file? Currently, I have been copying it over to another location and then running LogParser 2.2 against the log file. Instead, I would really like to run it against the live data so that I can see changes to it immediately, however, I am a little concerned that it might ca...

What is a good design for a query "layer" for Java JPA

In JPA the Entities are nice annotated Plain Old Java Objects. But I have not found a good way to interact with them and the database. In my current app, my basic design is always to have a sequence based id as primary key so I usually have to look up entities by other properties than PK. And for each Entity I have a stateless EJB of ...

Add new field to multiple table at once?

I want to add same field to multiple tables. Can I do this with single query in MySql? Thanks... ...

Encrypted data base query

I've just found out about stackoverflow.com and just checking if there are ideas for a constraint I'm having with some friends in a project. Although this is more a theoretical question to which I've been trying to find an answer for some time. I'm not much given into cryptography but if I'm not clear enough I'll try to edit/comment to ...

MYSQL: REGEXP single quotation matching

I am having a trouble matching a string in a MySQL-query with the following regex: I want it to match to this string: "Rue de l' Harmonie" SELECT id, street_name FROM street_names WHERE street_name REGEXP '^(rue[a-z]+[[:blank:]])((du|de|la|des|d[\']|l[\'])[[:blank:]])*[[:<:]]HARMONIE$' Anybody can give me a hint? ...

Oracle joins ( left outer, right, etc. :S )

I knew stackoverflow would help me for other than know what is the "favorite programming cartoon" :P This was the accepted answer by: Bill Karwin Thanks to all for the help ( I would like to double vote you all ) My query ended up like this ( this is the real one ) SELECT accepted.folio, COALESCE( inprog.activityin, accep...

Is there a way to write a group by query in LinqToSql grouping not on a scalar value?

I have those maps in my repository. public IQueryable<AwType> GetAwTypes() { return from awt in _db.AwTypes select new AwType { Id = awt.Id, Header = awt.Header, Description = awt.Description }; } public IQueryable<Aw> GetAws() { return from aw in _db.Aws select new Aw...

How do I parse a base query (à la Google Data) in Java?

I have a system where I query a REST / Atom server for documents. The queries are inspired by GData and look like : http://server/base/feeds/documents?bq=[type in {'news'}] I have to parse the "bq" parameter to know which type of documents will be returned without actually doing the query. So for example, bq=[type = 'news'] ...

Does field size affect query time?

My question is in regards to MySQL, but I also wonder how this affects other databases. I have several fields that are varchar(255) but my coworker insists if they were varchar(30) -- or any smaller size -- then queries would run faster. I'm not so sure, but if it's so I'll admit to it. :) Thanks. ...