December 11, 2010 at 11:06 am
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
December 11, 2010 at 11:10 am
Check out ROW_NUMBER() in BOL.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 11:15 am
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??
December 11, 2010 at 11:21 am
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.
December 11, 2010 at 11:29 am
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 !
December 11, 2010 at 11:36 am
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
December 11, 2010 at 11:43 am
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
December 11, 2010 at 11:52 am
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
December 11, 2010 at 12:30 pm
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 😉
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 12:34 pm
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
December 11, 2010 at 12:38 pm
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!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 12:58 pm
Thanks Jeff..
Progress has been good since our last chat !:-):-D:-)
December 11, 2010 at 1:28 pm
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
December 11, 2010 at 1:29 pm
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply