ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,811 through 9,825 (of 10,143 total)

RE: Need to remove duplicate records and get distinct values

San (7/15/2008)
Hi
I am sorry if someone has already posted this question and am asking again.
This problem is eating my head, I have a table
Table 1 -
1br
1er
2rt
3yh
2uj
3iu
3ol
Now I want only...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 16, 2008 at 8:57 am

#843845
RE: Need to remove duplicate records and get distinct values

karthikeyan (7/15/2008)
use distinct keyword
Interesting solution - care to elaborate?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 16, 2008 at 8:46 am

#843831
RE: Database Design for Online Examination System

vasaharshit (7/11/2008)
hi all,
Please help me out to design the database for online examination system.
You will need to provide much more...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 11, 2008 at 8:02 am

#841503
RE: Convert 3X2 rows,columns to 1X6 row.columns

Something like this?
CREATE TABLE #ContactDetails (ContactID INT, ContactMethod VARCHAR(5), ContactDetail VARCHAR(30))
INSERT INTO #ContactDetails (ContactID, ContactMethod, ContactDetail)
SELECT 1, 'Phone', '+1 413 2220144' UNION ALL
SELECT 1, 'Fax', '+1 413 2221144'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 10, 2008 at 4:40 am

#840718
RE: eliminating duplicates

harshil (7/8/2008)
Hi Jeff, In this query, the condition is rightly mentioned as rowno>1 and shouldn't be rowno=1
What the purpose of this query is to remove all duplicate records except one...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 8, 2008 at 7:52 am

#839500
RE: eliminating duplicates

harshil (7/8/2008)
Yes Mr. Actually I have read the requirements and I have correctly suggested the intended solution to delete duplicate records.
Although I have intentionally mentioned it for SQL SERVER...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 8, 2008 at 5:29 am

#839383
RE: Getting the Last Integer values

DECLARE @t nvarchar(16)
SET @t = 'T5e3st003'
SELECT RIGHT(@t, MIN(number)-1)
FROM Numbers
WHERE SUBSTRING(REVERSE(@t), number, 1) NOT IN ('1','2','3','4','5','6','7','8','9','0')
AND number < 10
DROP TABLE #test
CREATE TABLE #test (t nvarchar(16))
INSERT INTO #test (t)
SELECT 'T5e3st003'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 8, 2008 at 4:56 am

#839366
RE: Getting the Last Integer values

A single statement?
SET @T = '03' 😉
Or perhaps
SET @T = REVERSE(LEFT(REVERSE(@T),2))
Or even
SET @T = substring(@T, 6, 2)
All of these (and Gail's solution) will give the result "03". Is...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2008 at 8:00 am

#838901
RE: The Daily Commute

Two hours & three trains each way: Reading -> Virginia Water -> Weybridge -> Surbiton (Reading -> Basingstoke -> Surbiton as backup route). WFH two days a week so it...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2008 at 4:56 am

#838767
RE: Help Optimizing Query?

pop (7/1/2008)
Interesting, def a little shorter code-wise....no performance increase though...
How many rows are returned by this...
SELECT a.casenum, a.at_id
FROM #audit_trail a
INNER JOIN (SELECT MAX(at_id) AS at_id, casenum FROM...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 2, 2008 at 9:05 am

#837295
RE: eliminating duplicates

d_sysuk (7/2/2008)
Hi Saritha,
Think you may have made the first initial mistake that I did, (whilst was determining what the poster required - as was not the clearest)..
Background
Table: Has dup rows
Requirement:
Delete...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 2, 2008 at 3:57 am

#837076
RE: eliminating duplicates

saritha (7/2/2008)
Try this
set rowcount 1
select 1
while @@rowcount > 0
delete Emp1 where 1 < (select count(*) from Emp1 a2
where Emp1.Eno = a2.Eno
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 2, 2008 at 3:48 am

#837073
RE: eliminating duplicates

karthikeyan (7/2/2008)
Jeff,
Heh... this all reminds me of another of my favorite 4 letter acronyms... RTFS!!!!!
what do you mean by RTFS ?
Read the friggin' spec 😛
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 2, 2008 at 2:58 am

#837055
RE: eliminating duplicates

rbarryyoung (7/1/2008)
Though don't forget that you need a covering Clustered Index to make this work.
Thanks for pointing that out Barry. This solution will only work if the data is ordered...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 2, 2008 at 2:35 am

#837048
RE: Help Optimizing Query?

Hi Pop
SELECT a.casenum, a.audit_date, c.audit_date as StartDate, a.stepname
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 1, 2008 at 10:57 am

#836827

Viewing 15 posts - 9,811 through 9,825 (of 10,143 total)