This is probably a really easy question, it's just very hard to google a word like "All". SELECT a.Id, Max(b.Number) FROM Table1 a JOIN Table2 b ON a.FK = b.Id GROUP BY a.Id But I want to add a where clause that specifies that all b.Id's linked to an a.FK must have values. So basically I don't want to select the a.Id grouping of b...
I have read that it is a good idea to have one file per CPU/CPU Core so that SQL can more efficiently stream data to and from the disks. Ok, I can see the benefit if they are on different spindles, but what if I only have one spindle (4 drives in Raid 10) for my data files (.mdf and .ndf), will I still benefit from splitting the data fi...
As well as CHAR (CHARACTER) and VARCHAR (CHARACTER VARYING), SQL offers an NCHAR (NATIONAL CHARACTER) and NVARCHAR (NATIONAL CHARACTER VARYING) type. In some databases, this is the better datatype to use for character (non-binary) strings: In SQL Server, NCHAR is stored as UTF-16LE and is the only way to reliably store non-ASCII charac...
I have a query that fetches employee's name, date, working hours, wage and calculate salary based on working hours * wage. The minutes in working hours that is being calculated are discarded. I only get the value in full hour. Snapshot example: My main concern is on workingHours and wageAmount workingHours is displayed as 5....
Here's my current named_scope. It returns an aggregate result from a number of rows. Sample result from 300 rows: Total Points: 3000 Wins: 31 Losses: 11 Here is the code: named_scope :open_for, lambda { |sportable_id, sportable_type, days_ago| { :conditions => ['sportable_id = ? and sportable_type = ? and game_time > ? ', sport...
Given two scenarios on SQL Server 2008/2005 - 1 Table has 5 rows 2 Table has 1 million rows If we need to update a few rows, what is is efficient and why? 1) UPDATE the required columns 2) DELETE the row and INSERT new row with the updated information ...
Assume i have the following decimal value: 4.584406 I need a simple quick way to truncate the decimal without rounding up, so the output would be 4.5 I'm using T-SQL (SQL Server 2005/2008). Any help will be appreciated. ...
Hi, sorry for the title but I'm not sure how I should call it. I'm using PostgreSQL 8.3 and would be fine with non-ansi query proposals. Suppose this schema: TimeEntries id - int entry_date - date tracked_seconds - int project_id - int projects id - int name - string path - st...
hi everybody, i am fresher in SQL,i want write a query to get the last 24 hour jobs record from msdb.dbo.sysjobhistory,but i cant get because,here i get the run_date and run_time as like the number.how i will convert the run_date and run_time into datetime and get the last 24 hour job record. i am using SQL Server 2000 thanks in advanc...
I'm about one click from getting this done and so any help would be great I have this sql code I wrote in Oracle: SELECT DISTINCT O.shipcountry, S.companyname, O.orderid, TO_CHAR(O.freight, '$999,999.999'), TO_CHAR(sum(unitprice)*count(quantity), '$999,999.99') as "Order Total" FROM corp...
I'm doing the following query SELECT * FROM downloads WHERE MATCH (title, artist) AGAINST ('+like ' IN BOOLEAN MODE) ...and it's not returning any results when it should be. It's not the code or anything, since it works fine for any other search terms. Could it be the fact that like is commonly used in MySQL queries so it's i...
How do you convert a SQL query with nested SELECT statements to a LINQ statement? I have the following SQL statement which outputs the results I need but I'm not sure how to replicate this in LINQ . SELECT X.ITMGEDSC, (SUM(X.[ENDQTY_I]) - SUM(X.[ORDERLINES])) AS AVAIL FROM SELECT T1.[MANUFACTUREORDER_I],T2.[ITMGEDSC],T1.[ENDQTY_I], (S...
If i have two tables like user table-"u" userid | name 1 | lenova 2 | acer 3 | hp pass table-"p" userid | password 1 | len123 2 | acer123 3 | hp123 as for as i learnt from tutorials I can join these 2 tables using many joins available in mysql as said here If i have a table like role table-"r" ...
Can anyone give me a sample script for batch insert/update of records in table using stored procedure in SQL Server? ...
I always find it confusing to add foreign keys to primary table in Management Studio. Lets say I have a Table1 { ID int, -- Primary Key Table2ID int, -- Refers to Table2's ID } Table2 { ID int, -- Primary Key SomeData nvarchar(50) } I am adding a foreign key to Table1 by Right Click -> Relationships -> Table and...
I have a client who ships fluids in USPS flat rate boxes. If you are unfamiliar with USPS flat rate boxes, they are boxes of a certain volume that ship regardless of weight. Anything that fits in the box, ships for one low price. My client uses two box sizes: the medium flat rate boxes, and the large flat rate boxes. Additionally, my cli...
I have heard joins should be preferred over nested queries. Is it true in general? Or there might be scenarios where one would be faster than other: for e.g. which is more efficient way to write a query?: Select emp.salary from employee emp where emp.id = (select s.id from sap s where s.id = 111) OR Select emp.salary from ...
Hi I have Xml File having 2000 node with values . I have a table on SQL Server that I want to move xml file node values [ 140 nodes ] every five minutes or so. What are your thoughts. Thanks ...
Hi, Is there a way in SQL Server that can show the Fiscal Year (begins on October 1 and ends on September 30) from a table which has a date column (1998 to 2010). Here is what I have done: select 'FY1999' as FY, site, count(*) from mytable where mydate >='10/1/1998' and mydate <'10/1/1999' group by site select 'FY2000' as FY, ...
In Cakephp is there a better way to write this: $unread_orders = $this->Order->find('all', array('conditions' => array('Order.status' => 'unread') )); $read_orders = $this->Order->find('all', array('conditions' => array('Order.status' => 'read') )); $dispatched = $this->Order->find('all', array('conditions' => array('Order.status' => 'd...