Selecting rows in a table.. not so simple!

Question

Selecting rows in a table.. not so simple!

Viewing 8 posts - 16 through 22 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

dwain.c (2/10/2014)
Jeff Moden (2/10/2014)
Regardless of the number of rows in the table, this can be done incredibly quickly IF you're only looking for one set that adds up to the desired number.
So... are you looking for just one set or more than one set that match the number?
It can also be done fairly quickly, regardless of the number of rows, if you want exclusive sets... that is, if something is used in oe set, they'll never be used again in any other set.
I just need to know which you want.
Are you thinking this is a special case of relational division?

Nothing quite so complex. You and Chris both know how I would do this. Things like this trully depend on the QUestion. 😉

--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)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

A man of mystery then. Not so easy to climb into your mind as you may think.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

marcipesa SSC Journeyman Points: 80 More actions · Answer 3

Many thanks to everybody!

I will try to (understand and) apply your suggestions ASAP!

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

marcipesa (2/11/2014)
Many thanks to everybody!
I will try to (understand and) apply your suggestions ASAP!

You should probably try to answer Jeff's questions. It is very likely he's got something superior in mind.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

marcipesa SSC Journeyman Points: 80 More actions · Answer 5

marcipesa

SSC Journeyman

Points: 80

February 11, 2014 at 4:14 am

#1687840

Just one set of rows would be enough!

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

dwain.c (2/10/2014)
A man of mystery then. Not so easy to climb into your mind as you may think.

Not so mysterious. I gave you a hint. Look at how the word "QUestion" is cased. 😛

--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)

Lempster SSCoach Points: 15651 More actions · Answer 7

Lempster

SSCoach

Points: 15651

February 18, 2014 at 4:53 am

#1689831

Quirky Update?

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

Lempster (2/18/2014)
Quirky Update?

That would be the ticket. It may take more than one pass but it would be nasty fast (heh... I'm a poet and don't know it).

--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)