May 7, 2015 at 5:21 pm
ROW,NOTE_ID, NOTE_SERIAL_NUM, NOTE_STATUS
1, 366834292, 333040947, 8
2, 366834292, 333054601, 2
3, 367096281, 333293527, 2
4, 367096281, 333282008, 8
5, 368005352, 111111111, 2
6, 369005341, 111111100, 8
I need from this subquery only the NOTE_IDs that have more than 1 row AND a Note Status of 8. BUT! It needs to return the NOTE_CSN_ID so I can later join to it from the outer query. I don't care about any NOTE_IDs that don't occur more than once.
ROW, NOTE_ID, NOTE_SERIAL_NUM, NOTE_STATUS
1, 366834292, 333040947, 8
2, 367096281, 333282008, 8
I am having a hard time because my outer query needs to join to the subquery on NOTE_SERIAL_NUMBER so I need to put NOTE_SERIAL_NUMBER in the select list of the subquery which throws my row_number over partion back to 1 from 2 when the Serial number wasn't there.
Thank you!
May 7, 2015 at 6:03 pm
Oh, c'mon... you've been around here long enough to know how to post data. Here's the table/inserts:
CREATE TABLE t1 (
n INT PRIMARY KEY,
note_ID INT,
Note_Serial_Num INT,
NoteStatus TINYINT);
INSERT INTO t1 VALUES
(1, 366834292, 333040947, 8),
(2, 366834292, 333054601, 2),
(3, 367096281, 333293527, 2),
(4, 367096281, 333282008, 8),
(5, 368005352, 111111111, 2),
(6, 369005341, 111111100, 8);
And where's your query that isn't working?
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 8, 2015 at 6:46 am
Vertigo44 (5/7/2015)
ROW,NOTE_ID, NOTE_SERIAL_NUM, NOTE_STATUS1, 366834292, 333040947, 8
2, 366834292, 333054601, 2
3, 367096281, 333293527, 2
4, 367096281, 333282008, 8
5, 368005352, 111111111, 2
6, 369005341, 111111100, 8
I need from this subquery only the NOTE_IDs that have more than 1 row AND a Note Status of 8. BUT! It needs to return the NOTE_CSN_ID so I can later join to it from the outer query. I don't care about any NOTE_IDs that don't occur more than once.
ROW, NOTE_ID, NOTE_SERIAL_NUM, NOTE_STATUS
1, 366834292, 333040947, 8
2, 367096281, 333282008, 8
I am having a hard time because my outer query needs to join to the subquery on NOTE_SERIAL_NUMBER so I need to put NOTE_SERIAL_NUMBER in the select list of the subquery which throws my row_number over partion back to 1 from 2 when the Serial number wasn't there.
Thank you!
What is NOTE_CSN_ID? I don't see this column in your "table" so it can't be returned.
Here's something that looks like it works:
DECLARE @t1 TABLE
(
n INT PRIMARY KEY,
note_ID INT,
Note_Serial_Num INT,
NoteStatus TINYINT
);
INSERT INTO @t1
VALUES
(1, 366834292, 333040947, 8),
(2, 366834292, 333054601, 2),
(3, 367096281, 333293527, 2),
(4, 367096281, 333282008, 8),
(5, 368005352, 111111111, 2),
(6, 369005341, 111111100, 8);
WITH test
AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY T.note_ID) AS ROW_NO
FROM
@t1 AS T
)
SELECT
*
FROM
test
WHERE
test.ROW_NO > 1 AND
test.NoteStatus = 8
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 8, 2015 at 6:51 am
pietlinden (5/7/2015)
Oh, c'mon... you've been around here long enough to know how to post data. Here's the table/inserts:
CREATE TABLE t1 (n INT PRIMARY KEY,
note_ID INT,
Note_Serial_Num INT,
NoteStatus TINYINT);
INSERT INTO t1 VALUES
(1, 366834292, 333040947, 8),
(2, 366834292, 333054601, 2),
(3, 367096281, 333293527, 2),
(4, 367096281, 333282008, 8),
(5, 368005352, 111111111, 2),
(6, 369005341, 111111100, 8);
And where's your query that isn't working?
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Yes you are right. I feel bad and will finally make time to go over the thread that shows us how to post properly this weekend. Sorry about that mess.
May 8, 2015 at 6:52 am
Jack Corbett (5/8/2015)
Vertigo44 (5/7/2015)
ROW,NOTE_ID, NOTE_SERIAL_NUM, NOTE_STATUS1, 366834292, 333040947, 8
2, 366834292, 333054601, 2
3, 367096281, 333293527, 2
4, 367096281, 333282008, 8
5, 368005352, 111111111, 2
6, 369005341, 111111100, 8
I need from this subquery only the NOTE_IDs that have more than 1 row AND a Note Status of 8. BUT! It needs to return the NOTE_CSN_ID so I can later join to it from the outer query. I don't care about any NOTE_IDs that don't occur more than once.
ROW, NOTE_ID, NOTE_SERIAL_NUM, NOTE_STATUS
1, 366834292, 333040947, 8
2, 367096281, 333282008, 8
I am having a hard time because my outer query needs to join to the subquery on NOTE_SERIAL_NUMBER so I need to put NOTE_SERIAL_NUMBER in the select list of the subquery which throws my row_number over partion back to 1 from 2 when the Serial number wasn't there.
Thank you!
What is NOTE_CSN_ID? I don't see this column in your "table" so it can't be returned.
Here's something that looks like it works:
DECLARE @t1 TABLE
(
n INT PRIMARY KEY,
note_ID INT,
Note_Serial_Num INT,
NoteStatus TINYINT
);
INSERT INTO @t1
VALUES
(1, 366834292, 333040947, 8),
(2, 366834292, 333054601, 2),
(3, 367096281, 333293527, 2),
(4, 367096281, 333282008, 8),
(5, 368005352, 111111111, 2),
(6, 369005341, 111111100, 8);
WITH test
AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY T.note_ID) AS ROW_NO
FROM
@t1 AS T
)
SELECT
*
FROM
test
WHERE
test.ROW_NO > 1 AND
test.NoteStatus = 8
Thanks Jack! I meant NOTE_CSN_ID
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply