TheSQLGuru (6/19/2013)

---

tssopa (6/19/2013)

---

Okay, I am a little dissapointed. I had read how efficient and fast recursive CTE's are compared to CURSOR's, however when I run the CTE against my...

This might work too.

WITH BigInts (n) AS (

SELECT CAST(9223372036854775807 AS BIGINT)

UNION ALL SELECT CAST(9223372036854775807 AS BIGINT))

SELECT SUM(CAST(n AS DECIMAL(38,0)))

FROM BigInts

Erland Sommarskog (7/4/2013)

---

This is easily achieved with CTE that numbers the rows and then you can filter on rowno = 1:

; WITH numbering AS (

SELECT RecID, DateEntered,...

What in Buddha's name is a "lac day?"

Since we're speaking hypothetically here and I don't have AdventureWorks to play with, I'll propose another hypothetical approach that I've seen to work well on occasion.

Using Gail's Query 1, instead...

You wouldn't be the first to call me crazy...

Mark-101232 (7/4/2013)

---

Interesting! However, the two queries don't give the same results. If you run this

delete from TABLE2 where Filename<>'file3.txt'

on the OPs sample data so that job1 only has one out...

I'm always interested in cases like this so with more time on my hands than I care to admit, I came up with the following test harness:

CREATE TABLE #TABLE1 ([filename]...

Here's possibly another way:

SELECT jobname

FROM TABLE1 a

CROSS APPLY (

SELECT TOP 1 SuccessFailIndicator=1-SuccessFailIndicator

FROM TABLE2 b

WHERE a.[Filename] = b.[Filename]

...

Coriolan,

The following matches your results by using a cross tab to generate counts of order detail lines by status.

WITH CountOfStatus AS (

SELECT ORderID

...

Similar to Luis's suggestion:

WITH Dummy(ID, String) AS(

SELECT 1,'SubnetAddress=10.16.224.128' UNION ALL

SELECT 2,'SubnetName=FOS-Salzburg Kasernenstraase #823 VPN' UNION ALL

SELECT 3,'SubnetMask=255.255.255.128' UNION ALL

SELECT 4,'NetworkAddress=10.0.0.0' UNION ALL

SELECT 5,'LocationID=895' UNION ALL

SELECT 30,'SubnetDescription=CHINA-BEIJING-C025 F2 XIANGJIANG CNVASX1706' UNION...

sqlfriends (7/3/2013)

---

If I have a street address that I would like to split street from Apt number, how could I do that?

for example now I have fullstreet ='100 NW 25...

Here's my take:

Q: Can I avoid using cursors?

A: Most probably.

Q: How?

A: DDL and sample data needed along with expected results to get some working code. An explanation of any...

Uhhh... Isn't this a case of using RANK() or DENSE_RANK(), depending on how ties are to be counted?

Lot's of assumptions here including all of Jeff's, Lowell's that you don't have an abbreviation like COR that matches to Corporation and that also there are not extra parens in...