sql

Can I have the sum() in one select?

I have e table like this : A B 1 1.5 1 1.5 2 2.3 2 2.3 2 2.3 3 1.5 3 1.5 how could i make the sum of column B, grouped by in 1.5, 2.3 and 1.5. in few words, I want to group by first and then make the sum(), but in one select. in this table, if you group by A column the result is: A B 1 1.5 2 2.3 3 1.5 now i want to sum() the B col...

SQL Query builder in Delphi

I need to give users the ability to build a simple SQL query against our database. Our application is written in Delphi. I am assuming only moderate levels of knowledge by the user, but they need the ability to build a simple select statement to be able to query against a couple of tables. If I can make this easy for them, that would ...

ROW_NUMBER() in MySQL

Is there a nice way in MySQL to replicate the MS SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 Then I could, for example, add a condition to limit intRow to 1 to get a single row with the highest col3 for each (col1, col...

Asset Database Design issues

I'm setting up an asset tracking database. Assets vary among black berries, PCs, servers, monitors, speakers, keyboards, mice, chairs, desks, cubicals, cubical walls, printers, refrigerators, microwaves... the whole range of things. That range would be my Category table: create table AssetManagement.zCategory( zCategoryId int identit...

Complicated query - help NEEDED!

Hi, really hope someone can help me on this one! I have 6 tables: Products prodid Prodequipment prodid equipclassmain equipclassor Equipclasses classid Equipfunctions equipid classid Equipment equipid Workshopequipment workshopid equipid Products – a list of some products Equipment – a list of some equipment Prodequipment – lists...

in Grails can you use both sql.newInstance and Domain class calls in the same method?

Can you have code that looks like this? def methodname () { proc = "sql to call a stored proc" def conn = Sql.newInstance(DB_CONN, DB_USERNAME, DB_PASSWORD, "org.postgresql.Driver") def result1 = conn.rows(proc) def result2 = MyClass.Find("from MyClass where foo='bar'") return [result1, result2] } If so are they using different connec...

MySQL Views: Referencing one calculated field (by name) in another calculated field

How can I define a view that has two calculated fields, for instance... ('TableName'.'BlueSquares' + 'TableName'.'RedSquares') AS TotalSquares, ('TableName'.'BlueCirles' + 'TableName'.'RedCircles') AS TotalCircles ... and create a third calculated field that's based on the first two calculated fields, as in... ('ViewName'.'TotalSqu...

Delete backup files older than 7 days

Using osql command, SSQL backup of a database is created. It is saved to Disk. Then renamed to match the date of the day backup was taken. All these files are saved in a single folder all the time. for example: Batch1.bat does the following 1) Created backup.bak 2) renamed to backup 12-13-2009.bak (this is done by the combination of % ~...

MS Access: searching a column for a star/asterisk

I'm looking for a way to search a column of string datatype which contains a * - the problem is that the star or asterisk is a reserved symbol. The following query doesn't work properly: select * from users where instr(pattern,"*") How can you write an Access query to search a column for an asterisk? ...

Applying Multiple Window Functions On Same Partition

Is it possible to apply multiple window functions to the same partition? (Correct me if I'm not using the right vocabulary) For example you can do SELECT name, first_value() over (partition by name order by date) from table1 But is there a way to do something like: SELECT name, (first_value() as f, last_value() as l (partition by na...

SQL Query :Filter help needed

I have 2 tables.One is OrderMaster and second is OrderDetails.Both are connected via OrderId Order table fields : OrderId(Primary Key),Total,OrderDate OrderDetail fields : OrderDetailId,ItemId,SupplierId,Amount,OrderId (ForiegnKey) One order can have multiple orderdetail records which can be from various suppliers Now i want to get O...

MS Access 2007 sql functions?

Does MS Access 2007 support creation of user defined sql functions? if so, where is the option for it in the menu? ...

SQL: Determine if one coordinate is in radius of another

Lets say i have a Table of rows that contain coordinates. what would be the best way to pull only the rows of coordinates which are in the radius of a another coordinate? To simplify my question, i'm giving the following example: Table like: Columns: Latitude, Longitude. Row1: 23.44444 24.55555 Row2: 32.44444 28.22222 Row3: ...

C# WPF SQL SELECT INNER JOIN

Im trying to join a user table to retrieve the users login name. I wish to have TWO INNER joins one for CreatedByUser_loginname and ModifiedByUser_loginname But at the moment Im just trying to get the SQL query string syntax right. BUT, When I change the name of the INNER JOIN with an AS 'name' I get an exception thrown when sqlreader()...

Moving and Deleting rows in MySQL

I have created two tables in MySQL which are called "birthTable" and "deathTable". When I add a person in deathTable which she/he was in birthTable, I want to delete his/her name and family from birthtable because I add him/her in deathTable. But I don't know how I can do that? ...

Query to get Max/Min row details for multiple fields

I have a table structure similar to the following example: DateTime V1 V2 V3 V4 10/10/10 12:10:00 71 24 33 40 10/10/10 12:00:00 75 22 44 12 10/10/10 12:30:00 44 21 44 33 10/10/10 12:20:00 80 11 88 12 With DateTime field being the unqiue and key field, I want...

Getting a boolean from a date compare in t-sql select

I am wondering if something along the lines of the following is possible in ms-sql (2005) SELECT (expiry < getdate()) AS Expired FROM MyTable WHERE (ID = 1) I basically want to evaluate the date compare to a boolean, is that possible in the select part of the statement? ...

How to efficiently stream an object graph as xml to sql server

I have an object graph that I need to serialize to xml and save to a Sql Server row. I have a table with an xml data type and I've been using the SqlXml datatype, providing it a memorystream. This works well for the most part. However, if my object graph is especially big (~200 meg in size), I get an OutOfMemoryException. What is the mo...

Sql Ordering Hiarchy

I am working on a SQL Statement that I can't seem to figure out. I need to order the results alphabetically, however, I need "children" to come right after their "parent" in the order. Below is a simple example of the table and data I'm working with. All non relevant columns have been removed. I'm using SQL Server 2005. Is there an ...

selecting only values having difference greater than 0 (no negatives and 0)

hi!! i've two columns in mysql db table votes : accept and reject i want to query only the top result after the accept-reject column values table : votes ================= accept | reject ================= 7 | 9 5 | 1 2 | 15 5 | 1 i want just the positive and top value, here 5-1 = 4 actually, i've lo...