January 25, 2010 at 2:24 pm
This is a 2 part question but let me proviode the scenario and then ask the 2 questions.
Below is the DDL of a ery simple table that is solely for articulating my scenario; it does not relfect a real world table we are using. I'm using thsi instead of our real structures because of NDA's.
The below table is populated on a regular interval, lets say nightly, via SQL Job with info on the tables in our DB.
DROP TABLE dbo.MY_TABLE_HISTORY
GO
CREATE TABLE dbo.MY_TABLE_HISTORY( hID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
dtAsOf DATETIME NOT NULL,
iObjectID NUMERIC(18,0) NOT NULL,
sSchema VARCHAR(255) NOT NULL,
sName VARCHAR(255) NOT NULL,
iRowCount INT NOT NULL,
fIndexKB INT NOT NULL,
fTableKB INT NOT NULL,
dtCreated DATETIME NOT NULL,
dtModified DATETIME NOT NULL,
sNotes VARCHAR(255) NULL,
CONSTRAINT [PK_MY_TABLE_HISTORY] PRIMARY KEY CLUSTERED ([hID] ASC)
/*WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)*/
ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE dbo.MY_TABLE_HISTORY ADD CONSTRAINT DF_MY_TABLE_HISTORY_dtAsOf DEFAULT (Getdate()) FOR dtAsOf
GO
#1) How to get the first and last rows from this table (for those rows within the specified date range and for the specified table) without using GROUP BY. - I know I could return the first and last rows of a date range per table listed in this history table by using a subquery that would return the Min(hID) and another that would return the Min(hID) but I'm trying to avoid sub-querys (personal preference).
Is there any way as of SQL Server 2005 (or possibly in 2008) to do something like this:
SELECT TH.*
FROM MY_TABLE_HISTORY TH
WHERE 1 = 1
AND TH.sName = 'BIGTABLE_A'
AND TH.dtAsOf Between @dtStart AND @dtEnd
AND ( TH.hID = FIRST(TH.hID)
OR
TH.hID = LAST(TH.hID)
)
Now the above isn't real T-SQL; the FIRST() and LAST() items are meant to infer matching the hID column to the first or lowest value hID within the set of rows retiurned and the last or highest value hID.
Q#2: Is there any commonly used name or term to refer for this kind of query, where you are looking for the firt/last or Top/Bottom N rows based on some mathcning column ?
Kindest Regards,
Just say No to Facebook!January 26, 2010 at 7:09 am
Have you tried using the WINDOWING functions, ROW_NUMBER(), etc...? Something like:
;WITH cteFirstLast AS
(
SELECT
hID,
ROW_NUMBER() OVER (ORDER BY dtAsOf) AS FIRST,
ROW_NUMBER() OVER (ORDER BY dtAsOf DESC) AS LAST
FROM
dbo.MY_TABLE_HISTORY AS MTH
WHERE
dtAsOf Between @startdate and @enddate
)
SELECT
hID,
dtAsOf,
iObjectID,
sSchema,
sName,
iRowCount,
fIndexKB,
fTableKB,
dtCreated,
dtModified,
sNotes
FROM
dbo.MY_TABLE_HISTORY AS MTH JOIN
cteFirstLast F ON
MTH.hID = F.hID
WHERE
F.first = 1
UNION ALL
SELECT
hID,
dtAsOf,
iObjectID,
sSchema,
sName,
iRowCount,
fIndexKB,
fTableKB,
dtCreated,
dtModified,
sNotes
FROM
dbo.MY_TABLE_HISTORY AS MTH JOIN
cteFirstLast F ON
MTH.hID = F.hID
WHERE
F.Last = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 27, 2010 at 6:57 am
DECLARE @table table
(
IDVARCHAR(100),
DATETIME1DATETIME
)
INSERT INTO @table
SELECT
'AI1', 40001
UNION ALL
SELECT
'AI1', 40002
UNION ALL
SELECT
'AI1' ,40003
UNION ALL
SELECT
'AI1' ,40004
UNION ALL
SELECT
'AI1', 40005
UNION ALL
SELECT
'AI1', 40006
UNION ALL
SELECT
'AI2', 41001
UNION ALL
SELECT
'AI2', 41002
UNION ALL
SELECT
'AI2', 41003
UNION ALL
SELECT
'AI2', 41004
UNION ALL
SELECT
'AI2', 41005
UNION ALL
SELECT
'AI2', 41006
UNION ALL
SELECT
'AI3', 42001
UNION ALL
SELECT
'AI3', 42002
UNION ALL
SELECT
'AI3', 42003
UNION ALL
SELECT
'AI3', 42004
UNION ALL
SELECT
'AI3',42005
UNION ALL
SELECT
'AI3', 42006
SELECT
* FROM
@tableT1
WHERE
DATETIME1IN
(
SELECT MAX(DATETIME1) AS DATETIME1FROM
@tableT2
UNION ALL
SELECT MIN(DATETIME1) AS DATETIME1FROM
@tableT2
)
Regards,
Mitesh OSwal
+918698619998
January 27, 2010 at 1:06 pm
Jack Corbett (1/26/2010)
Have you tried using the WINDOWING functions, ROW_NUMBER(), etc...? Something like:
I have not but I will; thanks Jack!
Kindest Regards,
Just say No to Facebook!January 27, 2010 at 1:09 pm
Does this do what you need?
select top 1 *
from My_Table_History
where sName = 'BIGTABLE_A'
and dtAsOf between @dtStart and @dtEnd
order by hID
union
select top 1 *
from My_Table_History
where sName = 'BIGTABLE_A'
and dtAsOf between @dtStart and @dtEnd
order by hID desc;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2010 at 1:27 pm
Jack,
I just tested out the CTE and it does work when MY_TABLE_HISTORY has just one value for sNane or 1 table name. The problem is getting the first & last rows for each unique Table name or for each unique value of sName. This perhaps may be as simple as some small change to the ROW_NUMBER() but I haven't figured it out but then again I'm not that familiar with this approach. Any suggestions are greatly welcomed.
The end goal is to get the Oldest and most recent entry in this table for each Table and them use that to determine the change in several clumns between the oldest & newest row of data for each table.
Thanks
Kindest Regards,
Just say No to Facebook!January 27, 2010 at 1:37 pm
Sounds like you just need to use the PARTITION BY clause on the ROW_NUMBER() function.
Something like:
ROW_NUMBER() OVER (PARTITION BY sName ORDER BY dtAsOf)
PARTITION BY basically restarts the numbering whenever the value in the column(s) specified changes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 27, 2010 at 1:37 pm
January 27, 2010 at 1:37 pm
Jack beat me to it. 🙂
Regards,
Jason P. Burnett
Senior DBA
January 27, 2010 at 2:26 pm
Thanks guys, I knew it would be something simple.
Kindest Regards,
Just say No to Facebook!Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply