query: child of child of child of...: Which are the descendants?

Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)

query: child of child of child of...: Which are the descendants?

Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)sql

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?
Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

Query/Workload Generator

Hello,

- Is any body aware of a random workload/query generator such as the
TPC-H query generator (QGEN) ? I am looking for a query generator that
takes a schema as an input, and produces several queries.

I need it mainly for performance evaluation. the generator I am looking
for should produce SELECT queries, including joins, and not only INSERT
INTO, DELETE, UPDATE queries such as the "SqlQueryGenerator" at
http://www.tucows.com/preview/297930

Thanks

Regards,
Abdur-RahmanThe SQL 2000 Resource Kit has a tool called DB Hammer (or something
similar) which you can use to generate workloads. But I've never used
it myself, so I can't tell you if it's as easy as just pointing it at a
schema.

A general problem with tools which generate artificial workloads is
that they may not reflect your real workload patterns very well -
there's usually no way for the tool to know which tables or procs are
most frequently accessed. So another approach you might consider is
using Profiler to get a workload trace, then replay it in a test
environment while you gather whatever performance information you need.

Simon|||The SQL 2000 Resource Kit has a tool called DB Hammer (or something
similar) which you can use to generate workloads. But I've never used
it myself, so I can't tell you if it's as easy as just pointing it at a
schema.

A general problem with tools which generate artificial workloads is
that they may not reflect your real workload patterns very well -
there's usually no way for the tool to know which tables or procs are
most frequently accessed. So another approach you might consider is
using Profiler to get a workload trace, then replay it in a test
environment while you gather whatever performance information you need.

Simon

Query/View: The 2 newest periods for each indicator

Hi,

I'm working on a simple performance-program, where I need to extract
information from the 2 newest periods for every performance-indicator
- And from there calculate a trend between these results.

The problem is, that I can't find a simple way to extract the 2 latest
results.

The Table (Table1) looks like this:
kpiIDperiodIDActual
Acceleration23
Acceleration54
Speed1100
Speed4200
Speed7220
Speed9180
Weight122
Weight332
Weight721
Weight1033

If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):

SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);

BUT - how how do I get the second-newest period as well?

Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest

Alternatively a View with 2 posts for each performace-indicator.

Thanks in advance
RyanOn Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote:

>Hi,
>I'm working on a simple performance-program, where I need to extract
>information from the 2 newest periods for every performance-indicator
>- And from there calculate a trend between these results.
>The problem is, that I can't find a simple way to extract the 2 latest
>results.
>The Table (Table1) looks like this:
>kpiIDperiodIDActual
>Acceleration23
>Acceleration54
>Speed1100
>Speed4200
>Speed7220
>Speed9180
>Weight122
>Weight332
>Weight721
>Weight1033
>If I want to extract the newest I use something like this (made it in
>MS Access, so the syntax might differ slightly from SQLServer):
>SELECT table1.kpiID, table1.periodID, table1.Actual
>FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
>table1 as t WHERE t.kpiID=table1.kpiID);
>BUT - how how do I get the second-newest period as well?

Hi Ryan,

SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2

>Preferably I would like the final result to be a View with the
>following fields:
>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>Actual_sec_newest

In that case, try this instead:

SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
FROM table1 AS a
LEFT JOIN table1 AS b
ON b.kpiID = a.kpiID
AND b.periodID = (SELECT MAX(c.periodID)
FROM table1 AS c
WHERE c.kpiID = a.kpiID
AND c.periodID < a.periodID)
WHERE a.periodID = (SELECT MAX(t.periodID)
FROM table1 AS t
WHERE t.kpiID = a.kpiID)

(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks a lot. I got them both working without any hassle.

>SELECT a.kpiID, a.periodID, a.Actual
>FROM table1 AS a
>WHERE (SELECT COUNT(*)
> FROM table1 AS b
> WHERE b.kpiID = a.kpiID
> AND b.periodID >= a.periodID) <= 2

I find this to be quite clever - had to look at it some time to figure
out how it works.

>>
>>Preferably I would like the final result to be a View with the
>>following fields:
>>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>>Actual_sec_newest
>In that case, try this instead:
>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>FROM table1 AS a
>LEFT JOIN table1 AS b
> ON b.kpiID = a.kpiID
> AND b.periodID = (SELECT MAX(c.periodID)
> FROM table1 AS c
> WHERE c.kpiID = a.kpiID
> AND c.periodID < a.periodID)
>WHERE a.periodID = (SELECT MAX(t.periodID)
> FROM table1 AS t
> WHERE t.kpiID = a.kpiID)
Works as well - minor adjustment needed: Move lines 5-8 to the end.

Regards
Ryan|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

I am a little confused on this. Aren't "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.|||celko, have you actually ever HELPED anyone on this list???

i'd be curious to review a link where your original SQL code, written
in the past 10 years, is deomonstrated.

thx,
doug|||On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:

>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>FROM table1 AS a
>>LEFT JOIN table1 AS b
>> ON b.kpiID = a.kpiID
>> AND b.periodID = (SELECT MAX(c.periodID)
>> FROM table1 AS c
>> WHERE c.kpiID = a.kpiID
>> AND c.periodID < a.periodID)
>>WHERE a.periodID = (SELECT MAX(t.periodID)
>> FROM table1 AS t
>> WHERE t.kpiID = a.kpiID)
>>
>Works as well - minor adjustment needed: Move lines 5-8 to the end.

Hi Ryan,

That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.

This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.

Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.

--
Hugo Kornelis, SQL Server MVP|||On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>>FROM table1 AS a
>>>LEFT JOIN table1 AS b
>>> ON b.kpiID = a.kpiID
>>> AND b.periodID = (SELECT MAX(c.periodID)
>>> FROM table1 AS c
>>> WHERE c.kpiID = a.kpiID
>>> AND c.periodID < a.periodID)
>>>WHERE a.periodID = (SELECT MAX(t.periodID)
>>> FROM table1 AS t
>>> WHERE t.kpiID = a.kpiID)
>>>
>>Works as well - minor adjustment needed: Move lines 5-8 to the end.
>Hi Ryan,
>That changes the meaning of the query - the place where you put those
>lines dictates what will happen for a kpiID that has only one row.
>This one row is by definition the latest - but there's no second latest.
>If you use the query I suggested, you'll get this kpiID in your result,
>with it's only row as last measurement and NULLs as it's second latest
>measurement.
>Your version (after moving those rows) will exclude any kpiID with only
>one row. Only kpiIDs with two or more measurements will be displayed. If
>that is indeed your requirement, then you can safely move these lines.
>And you can change the LEFT JOIN in an INNER JOIN as well, to get some
>performance gain.

Hi Hugo,

thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.

Thanks again.
Ryan|||I've read 2 books written by joe celko, and and both have really helped
me. I frequently profile my code to see what it is doing. So he's
helped me.|||Go to the CMP archives for DBMS, DATABASE PROGRAMMING & DESIGN, and
INTELLIGENT ENTERPRISE magazines to go back over ten years. I have
written over 750 columns in the computer trade and academic press,
mostly dealing with data and databases. I currently write for BMC's
DBAzine.com e-magazine.

My six books: SQL FOR SMARTIES (Morgan-Kaufmann, 1995, second edition
1999, third edition 2005), SQL PUZZLES & ANSWERS (Morgan-Kaufmann,
1997), DATA & DATABASES (Morgan-Kaufmann, 1999) and TREES & HIERARCHIES
IN SQL (Morgan-Kaufmann, 2004) and SQL PROGRAMMING STYLE
Morgan-Kaufmann, 2005).
Past magazine columns include: "SQL Explorer" in DBMS (Miller-Freeman);
"Celko on SQL" in DATABASE PROGRAMMING & DESIGN (Miller-Freeman);
"WATCOM SQL Corner" in POWERBUILDER DEVELOPERS' JOURNAL (SysCon); "SQL
Puzzle" in BOXES & ARROWS (Frank Sweet Publishing); "DBMS/Report" in
SYSTEMS INTEGRATION (Cahner-Ziff); "Data Desk" in TECH SPECIALIST
(R&D); "Data Points" in PC TECHNIQUES (Coriolis Group); "Celko on
Software" in COMPUTING (VNC Publications, UK), "SELECT * FROM Austin"
(Array Publications, The Netherlands), and he was editor for the
"Puzzles & Problems" section of ABACUS (Springer-Verlag) and I ran the
CASEFORUM section 18, "Celko on SQL", on CompuServe.

So, what have you done?|||if it is SQL Server 2005, use row_number() *untested*:

select * from(
SELECT table1.kpiID, table1.periodID, table1.Actual,
row_number() over(partition by kpId order by periodId desc) rn
FROM table1
) t
where rn<3|||That's Joe Celko, the demi-god of SQL for pete's sake.sql