July 22, 2015 at 11:34 am
Hi Fellow Members.
I want to select all the Rows with Grouping done by PARENTID and Max value in REVNR per GROUP.
I am able to get one MAX row per Group but not all the Rows which has MAX value.
Here is my Table sample.
CREATE TABLE #TableA0 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT
,REVNR INT
,XREFID INT
);
INSERT INTO #TableA0 (iIndex,ParentID,DocumentID,Qty,RevNr,XrefID)
Select
'0' AS iIndex,'0' AS ParentID,'45' AS DocumentID,'0' AS Qty,'0' AS RevNr,'0' AS XrefID UNION ALL
SELECT '1','45','46','1','2','35' UNION ALL
SELECT '1','45','46','1','3','36' UNION ALL
SELECT '1','45','47','1','3','37' UNION ALL
SELECT '1','45','46','1','4','38' UNION ALL
SELECT '1','45','47','1','4','39' UNION ALL
SELECT '1','45','46','1','5','40' UNION ALL
SELECT '1','45','47','1','5','41' UNION ALL
SELECT '1','45','46','2','6','42' UNION ALL
SELECT '1','45','47','2','6','43' UNION ALL
SELECT '1','45','46','1','7','44' UNION ALL
SELECT '1','45','47','2','7','45' UNION ALL
SELECT '1','45','46','2','9','49' UNION ALL
SELECT '1','45','47','2','9','50' UNION ALL
SELECT '1','45','48','2','9','51' UNION ALL
SELECT '1','45','46','3','10','52' UNION ALL
SELECT '1','45','47','3','10','53' UNION ALL
SELECT '1','45','48','3','10','54' UNION ALL
SELECT '1','45','46','4','11','55' UNION ALL
SELECT '1','45','47','3','11','56' UNION ALL
SELECT '1','45','48','3','11','57' UNION ALL
SELECT '1','45','46','4','12','58' UNION ALL
SELECT '1','45','47','3','12','59' UNION ALL
SELECT '1','45','48','3','12','60' UNION ALL
SELECT '1','45','46','4','13','61' UNION ALL
SELECT '1','45','47','3','13','62' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','46','4','14','66' UNION ALL
SELECT '1','45','47','3','14','67' UNION ALL
SELECT '1','45','48','3','14','68' UNION ALL
SELECT '1','45','49','1','14','69' UNION ALL
SELECT '1','45','46','1','8','46' UNION ALL
SELECT '1','45','47','2','8','47' UNION ALL
SELECT '1','45','48','1','8','48' UNION ALL
SELECT '2','49','50','1','1','64' UNION ALL
SELECT '2','49','51','1','1','65'
select * from #TableA0
DROP TABLE #TableA0
In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.
Regards
Ravi T
July 22, 2015 at 11:48 am
santa326 (7/22/2015)
Hi Fellow Members.I want to select all the Rows with Grouping done by PARENTID and Max value in REVNR per GROUP.
I am able to get one MAX row per Group but not all the Rows which has MAX value.
Here is my Table sample.
CREATE TABLE #TableA0 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT
,REVNR INT
,XREFID INT
);
INSERT INTO #TableA0 (iIndex,ParentID,DocumentID,Qty,RevNr,XrefID)
Select
'0' AS iIndex,'0' AS ParentID,'45' AS DocumentID,'0' AS Qty,'0' AS RevNr,'0' AS XrefID UNION ALL
SELECT '1','45','46','1','2','35' UNION ALL
SELECT '1','45','46','1','3','36' UNION ALL
SELECT '1','45','47','1','3','37' UNION ALL
SELECT '1','45','46','1','4','38' UNION ALL
SELECT '1','45','47','1','4','39' UNION ALL
SELECT '1','45','46','1','5','40' UNION ALL
SELECT '1','45','47','1','5','41' UNION ALL
SELECT '1','45','46','2','6','42' UNION ALL
SELECT '1','45','47','2','6','43' UNION ALL
SELECT '1','45','46','1','7','44' UNION ALL
SELECT '1','45','47','2','7','45' UNION ALL
SELECT '1','45','46','2','9','49' UNION ALL
SELECT '1','45','47','2','9','50' UNION ALL
SELECT '1','45','48','2','9','51' UNION ALL
SELECT '1','45','46','3','10','52' UNION ALL
SELECT '1','45','47','3','10','53' UNION ALL
SELECT '1','45','48','3','10','54' UNION ALL
SELECT '1','45','46','4','11','55' UNION ALL
SELECT '1','45','47','3','11','56' UNION ALL
SELECT '1','45','48','3','11','57' UNION ALL
SELECT '1','45','46','4','12','58' UNION ALL
SELECT '1','45','47','3','12','59' UNION ALL
SELECT '1','45','48','3','12','60' UNION ALL
SELECT '1','45','46','4','13','61' UNION ALL
SELECT '1','45','47','3','13','62' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','46','4','14','66' UNION ALL
SELECT '1','45','47','3','14','67' UNION ALL
SELECT '1','45','48','3','14','68' UNION ALL
SELECT '1','45','49','1','14','69' UNION ALL
SELECT '1','45','46','1','8','46' UNION ALL
SELECT '1','45','47','2','8','47' UNION ALL
SELECT '1','45','48','1','8','48' UNION ALL
SELECT '2','49','50','1','1','64' UNION ALL
SELECT '2','49','51','1','1','65'
select * from #TableA0
DROP TABLE #TableA0
In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.
Regards
Ravi T
I'm sorry, but I don't really understand what you're looking for. Maybe you could show us what you're expecting from the data you used.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 22, 2015 at 12:26 pm
It sounds like you want to use the DENSE_RANK() function.
;
WITH table_ranked AS (
SELECT iINDEX, PARENTID, DOCUMENTID, QTY, REVNR, XREFID
,DENSE_RANK() OVER(PARTITION BY PARENTID ORDER BY REVNR DESC) AS dr
FROM #TableA0
)
SELECT tr.iINDEX, tr.PARENTID, tr.DOCUMENTID, tr.QTY, tr.REVNR, tr.XREFID
FROM table_ranked AS tr
WHERE tr.dr = 1
Generally, when you want to return columns that are neither in the grouping or aggregate, you want to use a CTE with one of the ranking functions. When you want exactly one row per group, you usually want ROW_NUMBER(); when you want multiple rows, you usually want DENSE_RANK().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 22, 2015 at 12:38 pm
Hi,
I should have been cleared in my Description.
What I want is the following.
1. Group by ParentID and only select the MAX RevNR for Each Group.
The result will have all the rows containing.
ParentID 45 and REVNR 14 (4 Rows)
+
PARENTID 49 and REVNR 1 (2 Rows)
CREATE TABLE #TableA1 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT
,REVNR INT
,XREFID INT
);
INSERT INTO #TableA1 (iIndex,ParentID,DocumentID,Qty,RevNr,XrefID)
Select
'1' AS iIndex,'45' AS ParentID,'46' AS DocumentID,'4' AS Qty,'14' AS RevNr,'66' AS XrefID UNION ALL
SELECT '1','45','47','3','14','67' UNION ALL
SELECT '1','45','48','3','14','68' UNION ALL
SELECT '1','45','49','1','14','69' UNION ALL
SELECT '2','49','50','1','1','64' UNION ALL
SELECT '2','49','51','1','1','65'
select * from #TableA1
DROP TABLE #TableA1
Regards
Ravi T
July 22, 2015 at 12:46 pm
Does this query give you the results you're looking for?
select PARENTID, Max(RevNr) AS [Max_RevNr]
from #TableA0
GROUP BY PARENTID
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 22, 2015 at 12:48 pm
Did you check out my post? My results are exactly like yours except that I also have a row for ParentID = 0, and it's not clear why you don't want that included in the results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 22, 2015 at 10:00 pm
drew.allen (7/22/2015)
It sounds like you want to use the DENSE_RANK() function.
;
WITH table_ranked AS (
SELECT iINDEX, PARENTID, DOCUMENTID, QTY, REVNR, XREFID
,DENSE_RANK() OVER(PARTITION BY PARENTID ORDER BY REVNR DESC) AS dr
FROM #TableA0
)
SELECT tr.iINDEX, tr.PARENTID, tr.DOCUMENTID, tr.QTY, tr.REVNR, tr.XREFID
FROM table_ranked AS tr
WHERE tr.dr = 1
Generally, when you want to return columns that are neither in the grouping or aggregate, you want to use a CTE with one of the ranking functions. When you want exactly one row per group, you usually want ROW_NUMBER(); when you want multiple rows, you usually want DENSE_RANK().
Drew
The OVER() clause works on normal aggregate functions as well.
The query can be written without a CTE as:
SELECT iINDEX, PARENTID, DOCUMENTID, QTY, REVNR, XREFID,
MAX(REVNR) OVER(PARTITION BY PARENTID) AS dr
FROM #TableA0
Adding in some more columns to show other aggregate functions and the OVER() clause:
SELECT iINDEX, PARENTID, DOCUMENTID, QTY, REVNR, XREFID,
MAX(REVNR) OVER(PARTITION BY PARENTID) AS [MaxRevByParentID],
MAX(REVNR) OVER(PARTITION BY PARENTID, DOCUMENTID) AS [MaxRevByParentAndDocID],
COUNT(*) OVER(PARTITION BY PARENTID) AS [CountByParentID],
SUM(QTY) OVER(PARTITION BY PARENTID) AS [SumOfQtyByParentID],
CONVERT(decimal(5, 1), AVG(QTY * 1.0)
OVER(PARTITION BY PARENTID)) AS [AvgQtyByParentID],
COUNT(*) OVER() As [TotalCount]
FROM #TableA0
Note the use of OVER() with empty parentheses to indicate the entire resultset.
That should save the extra code and RANK()/filter work.
-Eddie
Eddie Wuerch
MCM: SQL
July 23, 2015 at 6:58 am
I'm not sure anyone understood the original posters requirement, which is to show all rows that match the max value. The one "sticking point" is that he didn't mention anything about PARENTID 0 when he stated the desired results, and there are a couple of ways to exclude that row. Here's my code:
CREATE TABLE #TableA0 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT
,REVNR INT
,XREFID INT
);
INSERT INTO #TableA0 (iIndex,ParentID,DocumentID,Qty,RevNr,XrefID)
SELECT '0' AS iIndex,'0' AS ParentID,'45' AS DocumentID,'0' AS Qty,'0' AS RevNr,'0' AS XrefID UNION ALL
SELECT '1','45','46','1','2','35' UNION ALL
SELECT '1','45','46','1','3','36' UNION ALL
SELECT '1','45','47','1','3','37' UNION ALL
SELECT '1','45','46','1','4','38' UNION ALL
SELECT '1','45','47','1','4','39' UNION ALL
SELECT '1','45','46','1','5','40' UNION ALL
SELECT '1','45','47','1','5','41' UNION ALL
SELECT '1','45','46','2','6','42' UNION ALL
SELECT '1','45','47','2','6','43' UNION ALL
SELECT '1','45','46','1','7','44' UNION ALL
SELECT '1','45','47','2','7','45' UNION ALL
SELECT '1','45','46','2','9','49' UNION ALL
SELECT '1','45','47','2','9','50' UNION ALL
SELECT '1','45','48','2','9','51' UNION ALL
SELECT '1','45','46','3','10','52' UNION ALL
SELECT '1','45','47','3','10','53' UNION ALL
SELECT '1','45','48','3','10','54' UNION ALL
SELECT '1','45','46','4','11','55' UNION ALL
SELECT '1','45','47','3','11','56' UNION ALL
SELECT '1','45','48','3','11','57' UNION ALL
SELECT '1','45','46','4','12','58' UNION ALL
SELECT '1','45','47','3','12','59' UNION ALL
SELECT '1','45','48','3','12','60' UNION ALL
SELECT '1','45','46','4','13','61' UNION ALL
SELECT '1','45','47','3','13','62' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','48','3','13','63' UNION ALL
SELECT '1','45','46','4','14','66' UNION ALL
SELECT '1','45','47','3','14','67' UNION ALL
SELECT '1','45','48','3','14','68' UNION ALL
SELECT '1','45','49','1','14','69' UNION ALL
SELECT '1','45','46','1','8','46' UNION ALL
SELECT '1','45','47','2','8','47' UNION ALL
SELECT '1','45','48','1','8','48' UNION ALL
SELECT '2','49','50','1','1','64' UNION ALL
SELECT '2','49','51','1','1','65';
SELECT *
FROM #TableA0;
WITH GROUPED_VALUES AS (
SELECT A.PARENTID, MAX(A.REVNR) AS REVNR
FROM #TableA0 AS A
GROUP BY A.PARENTID
HAVING COUNT(*) > 1
)
SELECT TA.*
FROM GROUPED_VALUES AS GV
INNER JOIN #TableA0 AS TA
ON GV.PARENTID = TA.PARENTID
AND GV.REVNR = TA.REVNR;
DROP TABLE #TableA0
I chose to use HAVING in case the OP needs to ensure each grouping has at least 2 rows, but it might be better as a WHERE clause instead, specifically eliminating that particular PARENTID value. Let us know, Ravi...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply