I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
Maury
Maury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>
|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury
|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.
|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label row. Show all posts
Showing posts with label row. Show all posts
Thursday, March 8, 2012
"second newest" record?
I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"second newest" record?
I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. I
n
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum i
d
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grr
r.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time serie
s
>-- and extensively benchmarked them on our 2 million+ row prices database.
In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum
id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Gr
rr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can'
t
seem to find it. It might be possible to compare the side (buy/sell) with th
e
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true
:
>they DO sometimes put the correction as the second record, and sometimes th
e
>third. I've looked through the data for some sort of determinant, but I can
't
>seem to find it. It might be possible to compare the side (buy/sell) with t
he
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
-- and extensively benchmarked them on our 2 million+ row prices database. I
n
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum i
d
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grr
r.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time serie
s
>-- and extensively benchmarked them on our 2 million+ row prices database.
In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum
id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Gr
rr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can'
t
seem to find it. It might be possible to compare the side (buy/sell) with th
e
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true
:
>they DO sometimes put the correction as the second record, and sometimes th
e
>third. I've looked through the data for some sort of determinant, but I can
't
>seem to find it. It might be possible to compare the side (buy/sell) with t
he
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"pivotting" a table...
Greetings!
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
--
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
--
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> Greetings!
>> I've got a simple list of id's and descriptions that I'd like to rotate
>> so that each column is the id there exists one row which is the
>> description for that column.
>> Basically something like this:
>> ID Description
>> --
>> 1 Bob
>> 2 Franks
>> 3 Harry
>> And I'd like this:
>> 1 2 3
>> --
>> Bob Franks Harry
>>
>> I looked at the PIVOT operator with SQL Server 2005 but this seems more
>> for aggregating data. What SQL would you use to accomplish this?
>> Thanks.
>> Daniel.
>|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> Dan
>> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
>> GO
>> INSERT INTO #tmp VALUES (1,'Bob')
>> INSERT INTO #tmp VALUES(2,'Franks')
>> INSERT INTO #tmp VALUES(3,'Harry')
>>
>> SELECT *
>> FROM #tmp
>> PIVOT
>> (
>> MAX(Description)
>> FOR ID IN([1],[2],[3])
>> ) AS PVT
>> "Dan Bass" <na> wrote in message
>> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> Greetings!
>> I've got a simple list of id's and descriptions that I'd like to rotate
>> so that each column is the id there exists one row which is the
>> description for that column.
>> Basically something like this:
>> ID Description
>> --
>> 1 Bob
>> 2 Franks
>> 3 Harry
>> And I'd like this:
>> 1 2 3
>> --
>> Bob Franks Harry
>>
>> I looked at the PIVOT operator with SQL Server 2005 but this seems more
>> for aggregating data. What SQL would you use to accomplish this?
>> Thanks.
>> Daniel.
>>
>
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
--
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
--
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> Greetings!
>> I've got a simple list of id's and descriptions that I'd like to rotate
>> so that each column is the id there exists one row which is the
>> description for that column.
>> Basically something like this:
>> ID Description
>> --
>> 1 Bob
>> 2 Franks
>> 3 Harry
>> And I'd like this:
>> 1 2 3
>> --
>> Bob Franks Harry
>>
>> I looked at the PIVOT operator with SQL Server 2005 but this seems more
>> for aggregating data. What SQL would you use to accomplish this?
>> Thanks.
>> Daniel.
>|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> Dan
>> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
>> GO
>> INSERT INTO #tmp VALUES (1,'Bob')
>> INSERT INTO #tmp VALUES(2,'Franks')
>> INSERT INTO #tmp VALUES(3,'Harry')
>>
>> SELECT *
>> FROM #tmp
>> PIVOT
>> (
>> MAX(Description)
>> FOR ID IN([1],[2],[3])
>> ) AS PVT
>> "Dan Bass" <na> wrote in message
>> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> Greetings!
>> I've got a simple list of id's and descriptions that I'd like to rotate
>> so that each column is the id there exists one row which is the
>> description for that column.
>> Basically something like this:
>> ID Description
>> --
>> 1 Bob
>> 2 Franks
>> 3 Harry
>> And I'd like this:
>> 1 2 3
>> --
>> Bob Franks Harry
>>
>> I looked at the PIVOT operator with SQL Server 2005 but this seems more
>> for aggregating data. What SQL would you use to accomplish this?
>> Thanks.
>> Daniel.
>>
>
"pivotting" a table...
Greetings!
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.
Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>
|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>
|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.
Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>
|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>
|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
"pivotting" a table...
Greetings!
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
--
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
--
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
--
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
--
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
Saturday, February 25, 2012
"Max row" in each group
I'm having trouble developing a query that will group on a given set of
columns and, for each group, will also display the other columns in the
"max row" for that group. Let me illustrate what I mean:
CREATE TABLE [dbo].[GroupTest] (
[testID] [int] IDENTITY (1, 1) NOT NULL ,
[office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startDate] [datetime] NOT NULL ,
[status] [int] NOT NULL ,
[endDate] [datetime] NULL ,
[reportDate] [datetime] NOT NULL ,
[amount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupTest] ADD
CONSTRAINT [PK_GroupTest] PRIMARY KEY CLUSTERED
(
[testID]
) ON [PRIMARY]
GO
Each row in this table represents a status update in an issue tracking
system. (The actual table has more columns. This is a subset for
illustrative purposes.) None of the columns is unique other than testID.
I am being asked to produce a query for each combination of {office,
startDate} will include the row matching that combination that has the
greatest value in the status column. So if office='ABC' and
startDate='2005-11-30' and there are two rows matching these values, one
with status=3 and one with status=4, the query should include the row
having status=4.
If the combination {office, startDate, status} were unique in this
table, the following query would do the job:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
order by G0.office, G0.startDate
But that's not the case. I asked the customer what to do. For their
purposes, the report will be fine if it can show them an "illustrative"
row--in other words, if more than one row in the table matches a given
combination {office, startDate, status}, the query should return one of
them arbitrarily. But I'm not figuring out how to do that. I guess I'd
like to figure out how to retrieve the "maximum row" for each {office,
startDate} combination, or something similar. Can anyone help?
I could punt and go with just displaying the maximum over each of the
columns on the right-hand side (status, endDate, reportDate, amount),
each taken independently:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus,
max(endDate) maxEndDate,
max(reportDate) maxReportDate,
max(amount) maxAmount
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
AND G0.endDate = G1.maxEndDate
AND G0.reportDate = G1.maxReportDate
AND G0.amount = G1.maxAmount
order by G0.office, G0.startDate
But the rows produced by that query aren't "coherent": For a given
{office, startDate} combination, the status could be from one row, the
startDate could be from another, the endDate from another, etc. Each row
in my query should match an actual row in the table.I've moved on from my previous question, having figured out an approach.
However, I'm stuck on that approach because I'm getting unexpected
results. I'm asking about that in a new thread.
columns and, for each group, will also display the other columns in the
"max row" for that group. Let me illustrate what I mean:
CREATE TABLE [dbo].[GroupTest] (
[testID] [int] IDENTITY (1, 1) NOT NULL ,
[office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startDate] [datetime] NOT NULL ,
[status] [int] NOT NULL ,
[endDate] [datetime] NULL ,
[reportDate] [datetime] NOT NULL ,
[amount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupTest] ADD
CONSTRAINT [PK_GroupTest] PRIMARY KEY CLUSTERED
(
[testID]
) ON [PRIMARY]
GO
Each row in this table represents a status update in an issue tracking
system. (The actual table has more columns. This is a subset for
illustrative purposes.) None of the columns is unique other than testID.
I am being asked to produce a query for each combination of {office,
startDate} will include the row matching that combination that has the
greatest value in the status column. So if office='ABC' and
startDate='2005-11-30' and there are two rows matching these values, one
with status=3 and one with status=4, the query should include the row
having status=4.
If the combination {office, startDate, status} were unique in this
table, the following query would do the job:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
order by G0.office, G0.startDate
But that's not the case. I asked the customer what to do. For their
purposes, the report will be fine if it can show them an "illustrative"
row--in other words, if more than one row in the table matches a given
combination {office, startDate, status}, the query should return one of
them arbitrarily. But I'm not figuring out how to do that. I guess I'd
like to figure out how to retrieve the "maximum row" for each {office,
startDate} combination, or something similar. Can anyone help?
I could punt and go with just displaying the maximum over each of the
columns on the right-hand side (status, endDate, reportDate, amount),
each taken independently:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus,
max(endDate) maxEndDate,
max(reportDate) maxReportDate,
max(amount) maxAmount
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
AND G0.endDate = G1.maxEndDate
AND G0.reportDate = G1.maxReportDate
AND G0.amount = G1.maxAmount
order by G0.office, G0.startDate
But the rows produced by that query aren't "coherent": For a given
{office, startDate} combination, the status could be from one row, the
startDate could be from another, the endDate from another, etc. Each row
in my query should match an actual row in the table.I've moved on from my previous question, having figured out an approach.
However, I'm stuck on that approach because I'm getting unexpected
results. I'm asking about that in a new thread.
Sunday, February 19, 2012
"INSERT INTO" - Question
Hello,
if I have a row full of Data, lets call it Row "A". Now i have the "Insert Into" Command in my Application. So, when the User executes the Command again, will it update Row "A" or will it produce just a double?
This is important for my further Work with SQL.
I am using SQL Server 2006 Express or something like this. The Table in which Row "A" is has no primary key!
Greetz,
Eroli
Unless you have constraints/triggers or your INSERT violates the defaults/constraints, it will add a NEW ROW to the table irrespective of the existing data.
|||
Hi,
but when i put it all into a for-command like this one
for(int i = 0; i != 5;i++)
{
//All the Commands
}
I get only one row.
So, whats right now?
Greetz,
Eroli
|||Depends on what the commands are. Post all the relevant code and that might help.||| System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "INSERT INTO UsersTable([User], [FirstStart], [Commercial], [Name], [MailAddresse], [Addresse], [Addresse2], [Location], [PostCode], [Country], [PhoneNumber], [PhoneNumber2], [Sex], [ShowLastBoughts], [StartPage], [Notifications], [RemainingTime], [HomepageUrl], [Employees], [Owner], [Evaluations]) VALUES (@.User,@.FirstStart,@.Commercial, @.Name,@.MailAddresse,@.Addresse,@.Addresse2,@.Location,@.PostCode,@.Country,@.PhoneNumber,@.PhoneNumber2,@.Sex,@.ShowLastBoughts,@.StartPage,@.Notifications,@.RemainingTime,@.HomepageUrl,@.Employees,@.Owner,@.Evaluations)";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", -1);
SqlCommand.Parameters.AddWithValue("@.Commercial", -1);
SqlCommand.Parameters.AddWithValue("@.Name", "");
SqlCommand.Parameters.AddWithValue("@.MailAddresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse2", "");
SqlCommand.Parameters.AddWithValue("@.Location", "");
SqlCommand.Parameters.AddWithValue("@.PostCode", -1);
SqlCommand.Parameters.AddWithValue("@.Country", "");
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", -1);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", -1);
SqlCommand.Parameters.AddWithValue("@.Sex", "");
SqlCommand.Parameters.AddWithValue("@.ShowLastBoughts", "");
SqlCommand.Parameters.AddWithValue("@.StartPage", -1);
SqlCommand.Parameters.AddWithValue("@.Notifications", -1);
SqlCommand.Parameters.AddWithValue("@.RemainingTime", -1);
SqlCommand.Parameters.AddWithValue("@.HomepageUrl", "");
SqlCommand.Parameters.AddWithValue("@.Employees", -1);
SqlCommand.Parameters.AddWithValue("@.Owner", "");
SqlCommand.Parameters.AddWithValue("@.Evaluations", "");
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();
I've got only one Row.
|||
(1) Do you have any constraints/triggers on the table?
(2) Are you changing the values of the parameters for each loop or are youe xpecting to insert 5 rows with same values?
|||The User Column will be everytime the same, but maybe some other colums will change.
Is the UPDATE-Command better? When yes, so how can i use this?
Im confused, because i have now more rows when i tried my application again. Seems your are right.
|||You need to explain more clearly what you are trying to do and what you intend to do. INSERT and UPDATE are 2 different commands that accomplish 2 different things. so there's no question of one "better" over another. You need to use either one depending on what how application needs to behave. Simply, INSERT inserts data into the table (meaning rows will increase), UPDATE updates te existing data (and how many rows affected depends on the WHERE condition in the query).|||First my Application should create one row for one user. This can be done by using the INSERT Command, or not?
Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?
|||
Eroli:
First my Application should create one row for one user. This can be done by using the INSERT Command, or not?
Yes.
Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?
Yes. You need to do put your logic accordingly.
|||Ok, thanks for your advice!|||
Ok, i changed my Commands.
This is my UPDATE Command:
System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;
case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;
case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();</p><p>
It is in an ASP.NET Webapplication in C#! I get no failure, but nothing gets updated...
The obversely Row is filled with Dummy-Values before starting this Command.(MS SQL Server 2005 Express or so)
Greetz,
Eroli
|||I hope this looks better...
System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;
case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;
case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();
Ok, i solved it: I have just forgotten to put the [ ] round my Columns.
But there is another Problem: THe Condition does not work, because the User Column is no Identify-Column. How to solve this?
|||I did not understand your question. Can you rephrase?Thursday, February 16, 2012
"For each row" Trigger
Hi All,
The trigger in the following code is working on only last record
(sorry row) inserted . I wish to process "EACH ROW" in trigger as in
Oracle but without using cursor.
create table employee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
create table TreeEmployee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
GO
create trigger trgInsertOn on TreeEmployee for insert
as
BEGIN
DECLARE @.EMPID INT
DECLARE @.SUPERVISOR INT
SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
IF @.EMPID IS NOT NULL
BEGIN
INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
@.EMPID
END
END
GO
insert into employee
select 1,'Carl','Hogans',12
union
select 12,'Fred','Smith',NULL
union
select 16,'Sue','Bankers',1
union
select 26,'Frank','Green',12
union
select 55,'Karen','Feeders',NULL
union
select 56,'James','Black',12
union
select 57,'Kirk','Simmons',56
union
select 58,'Cliff','Page', 56
union
select 59,'Jimmy','Plant',56
union
select 60,'Jack','Cale', 59
union
select 61,'Robert','Santana',NULL
union
select 62,'Jack','Russell',1
INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
DROP TRIGGER TRGINSERTON
Drop table TreeEmployee
DROP table employee
With warm regards
Jatinder SinghINSERT INTO TreeEmployee
SELECT * FROM employee WHERE SUPERVISOR IN
(
Select EmpId from Inserted where Empid IS NOT NULL
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"jsfromynr" wrote:
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||What exactly are you trying to do? Maybe you should first read a book or two
an trees and hierarchies.
When trying to build trees with triggers you need to consider the fact that
SQL prevents execution of recursive triggers, and consider the fact that the
maximum nesting level for recursions in SQL 2000 is 32.
ML|||Hi ML,
Yes , you are right .
I had read the articles written by Joe Celko .
'IF @.EMPID IS NOT NULL ' will stop the insert if empid is null
and we can add if TRIGGER_NESTLEVEL(object_id('YourTrigger
Name')) =32
return . Can't we? I am looking for an alternative approach .
With warm regards
Jatinder Singh|||First it's imperative that you design a good tree model on paper. One of the
most important requirements of any data tree IMHO is to prevent circular
referencing.
In your case there are two entities you need to design appropriate storage
for:
1) employees - selecting a unique key for each employee (a primary key,
maybe) is imperative; and
2) employee_hierarchy - not only do employees supervise other employees and
answer to another employee, relationships may also be contextual (roles,
projects) - in this case hierarchy instances must be supported.
Think about that. Entity #2 is a data tree which references employees, and
is also a self-referenced entity.
Look at this example in my blog:
http://milambda.blogspot.com/2005/0...or-monkeys.html
You can use the function to create your own 'hiearchy-discovery' methods.
ML|||Hi ML,
Really Your blog is excellent!! What I can say about that ? I
read it earlier .
With warm regards
Jatinder Singh|||:)
I'm glad you find it useful.
ML|||I might be missing something here, but here it goes...
When you write a trigger it only fires once per 'triggering' event. So in
your trigger below, only one insert is being run, only one trigger will run.
Where are all my records? Triggers expost an 'inserted' table. Inside this
special table are all of the records that were inserted. There is also a
table called deleted, but for updates triggers use deleted and inserted
(this is a seperate topic).
So onto the meat and potatos, you need to treat the inserted table as a
table! It has multiple rows. So you have two options as I see it.
1) Use a cursor to iterate over each row in the instered table (yuk)
2) Rewrite your trigger to support multiple records.
Here's my stab at #2:
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM inserted
WHERE supervisor IS NOT NULL
END
GO
Now, having done that I have a few remarks... You shouldn't be storing
anything in the supervisor table other than the EmpID and SupID. They
should both be FK's back to the employee table. Also, the trigger needs to
be on the insert of the employee table NOT the TreeEmployee table.
I'm really not sure what your ultimate goal is here. Your TreeEmployee
simply replicates your employee table except where EmpId is null...
That should get you going!
HTH,
Ben
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||Ok I think I see my mistake (maybe...) Here is my corrected trigger... If
this works, I'd tweak it to handle updates and deletes as well...
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
-- start transaction to encapsulate the delete / insert
BEGIN TRANSACTION
-- to prevent dups, clear out inserted supervisors records from the tree
-- this also catches stale records
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
-- add anyone in the employee table to the TreeEmployee table
-- that has a newly inserted EmpId as a supervisor on them
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM employee
WHERE supervisor IN
(
SELECT EmpId
FROM inserted
)
COMMIT TRANSACTION
END
GO
"Ben" <ben@.online.nospam> wrote in message
news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>I might be missing something here, but here it goes...
> When you write a trigger it only fires once per 'triggering' event. So in
> your trigger below, only one insert is being run, only one trigger will
> run.
> Where are all my records? Triggers expost an 'inserted' table. Inside
> this special table are all of the records that were inserted. There is
> also a table called deleted, but for updates triggers use deleted and
> inserted (this is a seperate topic).
> So onto the meat and potatos, you need to treat the inserted table as a
> table! It has multiple rows. So you have two options as I see it.
> 1) Use a cursor to iterate over each row in the instered table (yuk)
> 2) Rewrite your trigger to support multiple records.
> Here's my stab at #2:
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM inserted
> WHERE supervisor IS NOT NULL
> END
> GO
> Now, having done that I have a few remarks... You shouldn't be storing
> anything in the supervisor table other than the EmpID and SupID. They
> should both be FK's back to the employee table. Also, the trigger needs
> to be on the insert of the employee table NOT the TreeEmployee table.
> I'm really not sure what your ultimate goal is here. Your TreeEmployee
> simply replicates your employee table except where EmpId is null...
> That should get you going!
> HTH,
> Ben
>
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
>|||:( where does it end...
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
Needs to be
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT EmpId
FROM inserted
)
"Ben" <ben@.online.nospam> wrote in message
news:9cfb4$42f12d04$d8445835$11906@.FUSE.NET...
> Ok I think I see my mistake (maybe...) Here is my corrected trigger...
> If this works, I'd tweak it to handle updates and deletes as well...
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> -- start transaction to encapsulate the delete / insert
> BEGIN TRANSACTION
> -- to prevent dups, clear out inserted supervisors records from the tree
> -- this also catches stale records
> DELETE FROM TreeEmployee
> WHERE Supervisor IN
> (
> SELECT Supervisor
> FROM inserted
> )
> -- add anyone in the employee table to the TreeEmployee table
> -- that has a newly inserted EmpId as a supervisor on them
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM employee
> WHERE supervisor IN
> (
> SELECT EmpId
> FROM inserted
> )
> COMMIT TRANSACTION
> END
> GO
>
> "Ben" <ben@.online.nospam> wrote in message
> news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>
The trigger in the following code is working on only last record
(sorry row) inserted . I wish to process "EACH ROW" in trigger as in
Oracle but without using cursor.
create table employee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
create table TreeEmployee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
GO
create trigger trgInsertOn on TreeEmployee for insert
as
BEGIN
DECLARE @.EMPID INT
DECLARE @.SUPERVISOR INT
SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
IF @.EMPID IS NOT NULL
BEGIN
INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
@.EMPID
END
END
GO
insert into employee
select 1,'Carl','Hogans',12
union
select 12,'Fred','Smith',NULL
union
select 16,'Sue','Bankers',1
union
select 26,'Frank','Green',12
union
select 55,'Karen','Feeders',NULL
union
select 56,'James','Black',12
union
select 57,'Kirk','Simmons',56
union
select 58,'Cliff','Page', 56
union
select 59,'Jimmy','Plant',56
union
select 60,'Jack','Cale', 59
union
select 61,'Robert','Santana',NULL
union
select 62,'Jack','Russell',1
INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
DROP TRIGGER TRGINSERTON
Drop table TreeEmployee
DROP table employee
With warm regards
Jatinder SinghINSERT INTO TreeEmployee
SELECT * FROM employee WHERE SUPERVISOR IN
(
Select EmpId from Inserted where Empid IS NOT NULL
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"jsfromynr" wrote:
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||What exactly are you trying to do? Maybe you should first read a book or two
an trees and hierarchies.
When trying to build trees with triggers you need to consider the fact that
SQL prevents execution of recursive triggers, and consider the fact that the
maximum nesting level for recursions in SQL 2000 is 32.
ML|||Hi ML,
Yes , you are right .
I had read the articles written by Joe Celko .
'IF @.EMPID IS NOT NULL ' will stop the insert if empid is null
and we can add if TRIGGER_NESTLEVEL(object_id('YourTrigger
Name')) =32
return . Can't we? I am looking for an alternative approach .
With warm regards
Jatinder Singh|||First it's imperative that you design a good tree model on paper. One of the
most important requirements of any data tree IMHO is to prevent circular
referencing.
In your case there are two entities you need to design appropriate storage
for:
1) employees - selecting a unique key for each employee (a primary key,
maybe) is imperative; and
2) employee_hierarchy - not only do employees supervise other employees and
answer to another employee, relationships may also be contextual (roles,
projects) - in this case hierarchy instances must be supported.
Think about that. Entity #2 is a data tree which references employees, and
is also a self-referenced entity.
Look at this example in my blog:
http://milambda.blogspot.com/2005/0...or-monkeys.html
You can use the function to create your own 'hiearchy-discovery' methods.
ML|||Hi ML,
Really Your blog is excellent!! What I can say about that ? I
read it earlier .
With warm regards
Jatinder Singh|||:)
I'm glad you find it useful.
ML|||I might be missing something here, but here it goes...
When you write a trigger it only fires once per 'triggering' event. So in
your trigger below, only one insert is being run, only one trigger will run.
Where are all my records? Triggers expost an 'inserted' table. Inside this
special table are all of the records that were inserted. There is also a
table called deleted, but for updates triggers use deleted and inserted
(this is a seperate topic).
So onto the meat and potatos, you need to treat the inserted table as a
table! It has multiple rows. So you have two options as I see it.
1) Use a cursor to iterate over each row in the instered table (yuk)
2) Rewrite your trigger to support multiple records.
Here's my stab at #2:
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM inserted
WHERE supervisor IS NOT NULL
END
GO
Now, having done that I have a few remarks... You shouldn't be storing
anything in the supervisor table other than the EmpID and SupID. They
should both be FK's back to the employee table. Also, the trigger needs to
be on the insert of the employee table NOT the TreeEmployee table.
I'm really not sure what your ultimate goal is here. Your TreeEmployee
simply replicates your employee table except where EmpId is null...
That should get you going!
HTH,
Ben
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||Ok I think I see my mistake (maybe...) Here is my corrected trigger... If
this works, I'd tweak it to handle updates and deletes as well...
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
-- start transaction to encapsulate the delete / insert
BEGIN TRANSACTION
-- to prevent dups, clear out inserted supervisors records from the tree
-- this also catches stale records
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
-- add anyone in the employee table to the TreeEmployee table
-- that has a newly inserted EmpId as a supervisor on them
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM employee
WHERE supervisor IN
(
SELECT EmpId
FROM inserted
)
COMMIT TRANSACTION
END
GO
"Ben" <ben@.online.nospam> wrote in message
news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>I might be missing something here, but here it goes...
> When you write a trigger it only fires once per 'triggering' event. So in
> your trigger below, only one insert is being run, only one trigger will
> run.
> Where are all my records? Triggers expost an 'inserted' table. Inside
> this special table are all of the records that were inserted. There is
> also a table called deleted, but for updates triggers use deleted and
> inserted (this is a seperate topic).
> So onto the meat and potatos, you need to treat the inserted table as a
> table! It has multiple rows. So you have two options as I see it.
> 1) Use a cursor to iterate over each row in the instered table (yuk)
> 2) Rewrite your trigger to support multiple records.
> Here's my stab at #2:
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM inserted
> WHERE supervisor IS NOT NULL
> END
> GO
> Now, having done that I have a few remarks... You shouldn't be storing
> anything in the supervisor table other than the EmpID and SupID. They
> should both be FK's back to the employee table. Also, the trigger needs
> to be on the insert of the employee table NOT the TreeEmployee table.
> I'm really not sure what your ultimate goal is here. Your TreeEmployee
> simply replicates your employee table except where EmpId is null...
> That should get you going!
> HTH,
> Ben
>
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
>|||:( where does it end...
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
Needs to be
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT EmpId
FROM inserted
)
"Ben" <ben@.online.nospam> wrote in message
news:9cfb4$42f12d04$d8445835$11906@.FUSE.NET...
> Ok I think I see my mistake (maybe...) Here is my corrected trigger...
> If this works, I'd tweak it to handle updates and deletes as well...
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> -- start transaction to encapsulate the delete / insert
> BEGIN TRANSACTION
> -- to prevent dups, clear out inserted supervisors records from the tree
> -- this also catches stale records
> DELETE FROM TreeEmployee
> WHERE Supervisor IN
> (
> SELECT Supervisor
> FROM inserted
> )
> -- add anyone in the employee table to the TreeEmployee table
> -- that has a newly inserted EmpId as a supervisor on them
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM employee
> WHERE supervisor IN
> (
> SELECT EmpId
> FROM inserted
> )
> COMMIT TRANSACTION
> END
> GO
>
> "Ben" <ben@.online.nospam> wrote in message
> news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>
Thursday, February 9, 2012
"Cannot sort a row of size" how to resolve
I have a SQL Stored Procedure which select data from some tables. I worked on some rows but when I inserted one more row which had a large data- new row has inserted but I cannot select and order its record and SQLsrv raised this error
"Cannot sort a row of size 8154, which is greater than the allowable maximum of 8094"
any one, plz help me to Fix this.
Thanks alotThe row size extends a maximum page length - You will have to normailze the data further or reduce the attribute lengths - Can you post the table schema (DDL)?
"Cannot sort a row of size 8154, which is greater than the allowable maximum of 8094"
any one, plz help me to Fix this.
Thanks alotThe row size extends a maximum page length - You will have to normailze the data further or reduce the attribute lengths - Can you post the table schema (DDL)?
Subscribe to:
Posts (Atom)
