Programming SQL
in [Microsoft SQL Server]
|
Selecting from a table where my unique Key is not their uniquekey. Mufasa, In SQL Server 2005, it's not hard: with T as ( select *, row_number() over ( partition by your_unique_key order by their_unique_key ) as occurence from theTable ) select <columns you want> from T where occurence = 1 This selects the first occurrence in their-key order of ... 22 Jul 2008 14:45
how to use SUM [RESOLVED] "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:84888042-E96A-4F84-AA4D-62240AB49ED8(a)microsoft.com... Add WITH ROLLUP after the GROUP BY clause and it will add the summary row. In the SELECT list you can use the GROUPING function to check if the row has been added by ROLLUP and replace NULL w... 22 Jul 2008 14:45
Remove word from end of string Using SQL 2000 and 2005 servers We have a view that pulls the proj_name from the MS Project server. It sometimes includes .Published at the end of the name. Is there a way to trim a specific value from the end of the string? Thanks, littleccguy ... 22 Jul 2008 14:45
Selecting from a table where my unique Key is not their unique key. I've got a list of customers, customer names, ... from another db (I didn't design it). I want to get a list of all of the customers plus the first customer name. So if customer 1 appears twice (customer name = 'Fred' and 'John') I want the first one it finds. Is there an easy way to do this without spinnin... 22 Jul 2008 13:43
Procedure to insert into multiple tables Hello All, I have 3 tables solset with columns solset_ID and Solset name Solsubset solsubset_id, subset name subset_link subset_link_id, solset_id(from solset), solsubset_id(solsubset) I am trying to write an insert statement for the solsubset table and one for subset_link. my in... 22 Jul 2008 15:47
Using single character wildcards in stored procedure and allow a % We have a search form with multiple fields for searching. I've simplified the stored procedure below to only show a couple of fields and parameters. The users can search using the ? or _ for single character searches by entering them in the form. However, they also want to be able to use the wildcard % to use... 22 Jul 2008 17:50
Database Engine Tuning Advisor Hi, When I use "Database Engine Tuning Advisor" to analyze my stored procedure, it recommends that I drop all related indexes (that I believe they are useful) and add so many statistics. I wonder how accurate is "Database Engine Tuning Advisor"? Are its recommendations something that I should always trust? ... 22 Jul 2008 15:47
ResultSet Paging and count(*) performance Hi Ken, You can get the total record count by using the OVER keyword. For the code you provided, make changes like below. With Cust AS ( SELECT CustomerID, CompanyName, ROW_NUMBER() OVER (order by CompanyName) as RowNumber, COUNT(*) OVER () As TotalCount FROM Customers where Country in ('US', 'CN')) select *... 22 Jul 2008 12:41
select rowcount per group before value in group = null I need to select the top 3 rows per each group (xID -- order by xID). No problem here. But I need to select the count of rows in each group before a null value is encountered in the stat column -- ordering by xID CREATE TABLE #tmp1(rowID int Identity(1,1), xID int, x1 int, stat varchar(1)) INSERT INTO #tmp1... 22 Jul 2008 13:43 |