Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,266 through 35,280 (of 59,099 total)

RE: Filter a table to get unique rows in a certain logic

mister.magoo (1/23/2011)
Hope you can work around this because mine is way too slooooowww! and ugly!
Glad to hear that my code isn't the only slow and ugly one on this problem....
--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)

January 23, 2011 at 5:45 pm

#1277013
RE: Filter a table to get unique rows in a certain logic

Darned good try, Peter. But it doesn't hold up for other data ({edit} Magoo beat me to it while I was writing this post). I know... it's a...
--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)

January 23, 2011 at 5:40 pm

#1277012
RE: Filter a table to get unique rows in a certain logic

SwePeso (1/23/2011)
Can I throw in a bone too?
--===== Peso's code
;WITH cteSource(ID, SequenceID)
AS (
SELECTu.ID,
ROW_NUMBER() OVER (PARTITION BY u.theValue ORDER BY u.ID) AS SequenceID
FROMdbo.Mvts AS s
UNPIVOT(
theValue
FOR theCol IN (s.idMvt1, s.idMvt2, s.idMvt3)
) AS...
--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)

January 23, 2011 at 2:56 pm

#1277005
RE: Stored Procedure help

Depending on what you're really doing, the may be no need here for a separate stored procedure nor even a UDF...
SELECT t1.ReqID,
...
--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)

January 23, 2011 at 2:45 pm

#1277003
RE: Filter a table to get unique rows in a certain logic

Gah! Sorry CC... although your code works with the original data, it doesn't work when we insert a new row. Try yours with the following data...
DROP TABLE MVTS
...
--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)

January 23, 2011 at 10:14 am

#1276984
RE: Filter a table to get unique rows in a certain logic

ColdCoffee (1/22/2011)
@jeff, u are absolutely right.. i mis-read the requirement; from morning (its morning in INDIA now, as i write) i was thinking about that only.. So i sat and...
--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)

January 23, 2011 at 9:55 am

#1276982
RE: Filter a table to get unique rows in a certain logic

mister.magoo (1/22/2011)
In the interest of seeing a SQL solution even though you have a CLR solution...
I offer this
First just the solution (which assumes you have added an "ID" column)
SELECT *
FROM...
--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)

January 23, 2011 at 9:48 am

#1276980
RE: Are the posted questions getting worse?

Steve Jones - SSC Editor (1/22/2011)
Definitely not SQL related, but my kid got his black belt today. Very proud of him.
http://www.youtube.com/watch?v=DtIOe4IsUq8
Very cool. Congrats to both of you.
--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)

January 23, 2011 at 9:32 am

#1276979
RE: An Introduction to Database Design

kenambrose (1/22/2011)
<<I would imagine that the first thing you would do is explain the concept of entities and tables >>
Actually, no, not at all. The first thing I do...
--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)

January 23, 2011 at 9:13 am

#1276977
RE: Formatting Dates with 3 Character Months (SQL Spackle)

To everyone,
Thank you for the wonderful discussion and all the code folks posted. Because I've not actually had to work on code that needs to be sensitive to language...
--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)

January 23, 2011 at 9:04 am

#1276976
RE: Queries on Queries for Performance

sql_butterfly (1/22/2011)
I have few basic questions (Consider TableA as a very big table with over 3000000 rows):
1. Is this the right way to check some condition:
...
--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)

January 22, 2011 at 8:04 pm

#1276942
RE: Critique - The Modern Resume

Steve Jones - SSC Editor (1/20/2011)
I am wondering what people think of this title and description:
Abstract: Learn practical ways in this session that you can use to build your career...
--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)

January 22, 2011 at 7:28 pm

#1276941
RE: Critique - Common SQL Server Mistakes

Steve Jones - SSC Editor (1/20/2011)
This is a presentation that I have been working on and evolving at a few user groups. Is this abstract ok?
Abstract: This presentation looks at...
--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)

January 22, 2011 at 7:08 pm

#1276940
RE: NEED HELP SQL QUERY

Nicky james (1/21/2011)
Anyone plzz help me
Gladly... you just need one join per dimension. The following code does the trick (and thanks for making it so easy with the readily...
--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)

January 22, 2011 at 6:20 pm

#1276935
RE: Formatting Dates with 3 Character Months (SQL Spackle)

David McKinney (1/22/2011)
Well I didn't use your speciality, Jeff, the tally table...instead I used my speciality the CTE 😎
I was mucking about with recursive CTEs (actually I rather like them...
--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)

January 22, 2011 at 3:27 pm

#1276931

Viewing 15 posts - 35,266 through 35,280 (of 59,099 total)