Forum Replies Created
Viewing 15 posts - 1,276 through 1,290 (of 1,439 total)
-
RE: Tricky Query
WITH CTE AS(SELECT C1,C2,C3,C4,
ROW_NUMBER() OVER(PARTITION BY C1,C2 ORDER BY C3,C4) AS rn
FROM mytable)
SELECT C1,C2,C3,C4
FROM CTE
WHERE rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
RE: SQL Query Question
select [Invoice No],[Payment No],
[Payment Collected],
[Payment Date]
from TableB
where [Invoice No]...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: A different Distinct Query
Maybe this?
WITH cte AS (SELECT *,
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Is a cursor the best way to bulk insert to multiple tables with foreign key?
This may help, using the Sql Server 2005 OUTPUT clause. This assumes name is unique in importperson
declare @t table(name varchar(100),person_id bigint)insert into person(name)
output inserted.name,inserted.person_id into @t(name,person_id)
select name
from importperson
insert into SystemKeys(person_id,systemkey)
select...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Hierarchy Traversal
The CTE "RootIDs" traverses up the hierarchy to gets the root IDs. These are then given to your CTE which traverses back down the hierarchy picking up the IDs you...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Hierarchy Traversal
With RootIDs As (
Select Child_ID,Parent_ID
From #Site
Where Child_ID IN (3,10)
UNION ALL
Select a.Child_ID,a.Parent_ID
From #Site a
INNER JOIN RootIDs c ON a.Child_ID=c.Parent_ID
Where c.Child_ID<>c.Parent_ID),
MyCTE(Child_ID,Parent_ID)
As
(
Select
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Calculating and allocating serial numbers
You should be able to do this using sp_getapplock / sp_releaseapplock. Check BOL for details.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: SQL - To the Power of -1
Equivalent to 1.0/Rate
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Combining Rows into single one
WITH CTE AS (SELECT DISTINCT ID,NAME,STATUS
FROM Test)
SELECT c.ID,c.NAME,c.STATUS,
(SELECT t.ADDRESS AS "text()" FROM Test t WHERE t.ID=c.ID ORDER BY t.ADDRESS FOR XML PATH(''))
FROM CTE...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Get IDs of multiple inserted records
Suggest you look at the OUTPUT clause
DECLARE @ids TABLE (Name VARCHAR(20), ID INT)INSERT INTO Keywords (Name)
OUTPUT inserted.* INTO @ids
SELECT Name
FROM #NewKeywords
WHERE IDKeyword is null
SELECT * FROM @ids
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Count the number of spaces in a field
DECLARE @S VARCHAR(30)SET @S='99202 10060 99000 A6402'
SELECT LEN(@S)-LEN(REPLACE(@S,' ','')) AS NumberOfSpaces
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Help with a GROUPBY
SELECT Customer_Id,TestID
FROM dbo.Table_data
WHERE Customer_Id IN (
SELECT Customer_Id
FROM dbo.Table_data d
GROUP BY Customer_Id
HAVING COUNT(*)>1
AND COUNT(DISTINCT TestID) > 1)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: How to frame a query based on the following prblm
with cte as (select VehicleNo, TrackTime, row_number() over(partition by VehicleNo order by TrackTime) as rn
from mytable)
select a.VehicleNo,a.TrackTime
from CTE a
where not exists (select * from CTE b where b.VehicleNo=a.VehicleNo and b.rn=a.rn+1...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Grouping the occurance of value in sets.
create table ranges(rmin int, rmax int)
insert into ranges(rmin , rmax )
select 0,20 union all
select 21,40 union all
select 41,60 union all
select 61,80 union all
select 81,100
select cast(rmin as varchar(10))+'-'+cast(rmax as varchar(10)) as...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Find Aggregate value in lookup
As long as they both work correctly, it's up to you. You may want to check timings and execution plans in case there
are major differences. Also I think your version...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1,276 through 1,290 (of 1,439 total)