How can I get this selection in one TSQL run ?

Question

Post reply

How can I get this selection in one TSQL run ?

Digs

SSCertifiable

Points: 7333
More actions
December 11, 2010 at 11:06 am

#229570

IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
create table #Data([ID] [Int],[Sub][Int], [Data][Int])
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (1,1,102)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (2,1,65)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (3,1,85)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (4,1,73)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (5,2,12)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (6,2,234)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (7,2,25)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (8,2,2476)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (9,3,45)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (10,3,5)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (11,3,15)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (12,3,54)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (13,4,512)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (14,4,24)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (15,4,24)
INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (16,4,2)
CREATE CLUSTERED INDEX ip_tID ON #Data(ID)
SELECT * FROM #Data
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
How do I get this result, please click URL
http://screencast.com/t/AGME4aynAyh
The first row data, from the first record of the new [Sub] field ??
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Check out ROW_NUMBER() in BOL.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 2

What for... I have ID as a count down, How's ROW_NUMBER() gonna make a diff ???

All I need is a guru to spend 1 sec of his time to post some TSQL, on show how too??

LutzM SSC Guru Points: 107049 More actions · Answer 3

Digs (12/11/2010)
...All I need is a guru to spend 1 sec of his time to post some TSQL, on show how too??

... and your homework is done... 😉

If it's not homework: How much will YOU get paid for that 1sec someone else had to spend?

More serioulsy: Look up ROW_NUMBER syntax, especially the PARTITION BY part.

Another option would be using a subquery with a grouped MIN().

As a side note: I strongly recommend being not as demanding but trying to read up on the suggestion provided instead.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 4

I remember the more friendly days...I am sure as hell NOT doing home work, I am an old fart struggle with TSQL !:cool:

Come on be kind !

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

RowNumber with both partition by and order by will give you the position of each of the rows you want within the groupings, then you can filter on that. Rank() will work as well, as would denserank

Seriously, look up rownumber and see how it works, it's an exceedingly useful function, all the windowing functions are.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Digs SSCertifiable Points: 7333 More actions · Answer 6

IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;

create table #Data([ID] [Int],[Sub][Int], [Data][Int])

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (1,1,102)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (2,1,65)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (3,1,85)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (4,1,73)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (5,2,12)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (6,2,234)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (7,2,25)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (8,2,2476)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (9,3,45)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (10,3,5)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (11,3,15)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (12,3,54)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (13,4,512)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (14,4,24)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (15,4,24)

INSERT INTO #Data ([ID], [Sub],[Data]) VALUES (16,4,2)

CREATE CLUSTERED INDEX ip_tID ON #Data(ID);

WITH nTable AS(SELECT ROW_NUMBER() OVER (PARTITION BY [Sub] ORDER BY [ID] DESC) AS num,

[Data]

FROM #Data)

SELECT * FROM nTable WHERE num =1

IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;

Ok cracked it, but it requires two scans...Can It be done without a WITH table...or a #temp table

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You don't need a temp table, however this does require either a subquery or a CTE. A CTE isn't really a table (despite its name), it's a named subquery

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

Digs (12/11/2010)
I remember the more friendly days...I am sure as hell NOT doing home work, I am an old fart struggle with TSQL !:cool:
Come on be kind !

Hey Digs, I just saw your other (running totals) thread where you were advised to read an article. So you post up two problems and you're advised in both of them to RTFM. Frustration! Apologies if I appeared unhelpful, I guess after a while you take stuff for granted - I assumed that you would pick up the PARTITION BY within a minute or two of opening up the BOL section on ROW_NUMBER(). If you'd come from a SQL background then you might. Next time I'll be more helpful. It would only have taken a few seconds to scribble out some pseudocode.

Bet you learned some useful stuff reading the ROW_NUMBER() section in BOL though? 😛

Oh, and I'm an old fart too 😉

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Digs (12/11/2010)
What for... I have ID as a count down, How's ROW_NUMBER() gonna make a diff ???
All I need is a guru to spend 1 sec of his time to post some TSQL, on show how too??

Heh... you asked for "1 sec" and that's what they gave you. And, I can't blame them for thinking it may be "homework" because the answer is obvious to some folks. 😉 They also wanted you to teach yourself something so that you wouldn't forget it.

Since I "know you" from an offline conversation and I'm a fellow old-fart :-P, I know what you're going though. With that thought in mind, take a look at the following and see that the folks talking about using a CTE were correct. The following uses only a single scan...

WITH

cteEnumerate AS

(

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Sub ORDER BY ID DESC),

ID, Sub, Data

FROM #Data

)

SELECT ID, Sub, Data

FROM cteEnumerate

WHERE RowNum = 1

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

Jeff Moden (12/11/2010)
Digs (12/11/2010)
What for... I have ID as a count down, How's ROW_NUMBER() gonna make a diff ???
All I need is a guru to spend 1 sec of his time to post some TSQL, on show how too??
Heh... you asked for "1 sec" and that's what they gave you. And, I can't blame them for thinking it may be "homework" because the answer is obvious to some folks. 😉 They also wanted you to teach yourself something so that you wouldn't forget it.
Since I "know you" from an offline conversation and I'm a fellow old-fart :-P, I know what you're going though. With that thought in mind, take a look at the following and see that the folks talking about using a CTE were correct. The following uses only a single scan...
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Sub ORDER BY ID DESC),
ID, Sub, Data
FROM #Data
)
SELECT ID, Sub, Data
FROM cteEnumerate
WHERE RowNum = 1

Good Lord, I've been reincarnated as Jeff's fluffer!

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 11

Thanks Jeff..

Progress has been good since our last chat !:-):-D:-)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

ChrisM@home (12/11/2010)
Good Lord, I've been reincarnated as Jeff's fluffer!

Heh... a fine fluffer you'd be indeed. 😛 But it wasn't that. You posted right after I started my reply and hit the "post" button before I saw your email. Just think of it as two old-farts thinking alike at the same time. 😀

--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: 1004414 More actions · Answer 13

Digs (12/11/2010)
Thanks Jeff..
Progress has been good since our last chat !:-):-D:-)

Very cool. Thanks for the feedback on that.

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