A different Distinct Query

Question

Post reply

A different Distinct Query

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

D'oh! this isn't the first time I've given someone a 2005 solution to a 2000 problem, sorry about that.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

Alvin Ramard (10/6/2008)
Someone slap me please! All I keep thing about is using a ...... cursor.

Slap... slap, slap... [font="Arial Black"]SLAP![/font]

Heh... Pork chops away!!! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 3

Chris Harshman (10/11/2008)
D'oh! this isn't the first time I've given someone a 2005 solution to a 2000 problem, sorry about that.

Not your fault... this IS a 2005 forum. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 4

johncyriac (10/11/2008)
...this particular database is in SQL 2000

Heh... [font="Arial Black"]SLAP!!![/font]

Scott's method will absolutely be the fastest. But, just in case you can't do anything to the table or can't create a temp table for some reason, try this...

SELECT hi.St_No, hi.Year, hi.Month, hi.Sal

FROM (SELECT St_No, Year, Month, Sal, DATEADD(mm,Month,DATEADD(yy,Year-1900,0)) AS Date

FROM #t) lo

INNER JOIN

(SELECT St_No, Year, Month, Sal, DATEADD(mm,-1,DATEADD(mm,Month,DATEADD(yy,Year-1900,0))) AS Date

FROM #t) hi

ON (lo.Date = hi.Date

AND lo.St_No = hi.St_No

AND lo.Sal <> hi.Sal)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

johncyriac SSCommitted Points: 1542 More actions · Answer 5

Hi friends

Thank you all,for the time you spend over this query ,

these type queries are so common in reporting as well as spread sheet ,but no so common in SQL Programming , (Sum if there is a change ,Suppress if duplicated , ... )

Here its the time time to summarize

We can address this problem using

1. CTE

2. Sum /Tally Table Method

3. A select statement which refers to a sub table (select statement ) ,to compare the value with previous recrod

regards

john

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

Heh... Pork chops away!!!

And don't even bother to use a latex-free launcher.

John N Hick Ten Centuries Points: 1320 More actions · Answer 7

Just a note on this. I ran both Scott's and Jeff's solutions; Scott's returned 7 rows and Jeff's returned 5.