November 4, 2017 at 1:13 am
is it possible to dynamically find the row containing the largest numeric value to return only one row from each group of records in a sub query?
select * from table1 t1 inner join table2 t2 on t1.no = t2.no where t2.level = (select (the highest value in level) from table2.
table2 columns:
no, description, level
1, desc1,1
1, desc2, 2
2, desc3, 1
2, desc1, 2
2, desc2, 3
level can be anything from 1 to 7
the desired outcome in the above records would be 2 rows only:
1, desc2, 2
2, desc2, 3
the filter is finding the row with the largest level number.
table1 has only one record for each no.
ideally the sub query would be included in the join statement
any ideas?
Bruce
November 4, 2017 at 6:34 am
Bruce-12445 - Saturday, November 4, 2017 1:13 AMis it possible to dynamically find the row containing the largest numeric value to return only one row from each group of records in a sub query?select * from table1 t1 inner join table2 t2 on t1.no = t2.no where t2.level = (select (the highest value in level) from table2.
table2 columns:
no, description, level
1, desc1,1
1, desc2, 2
2, desc3, 1
2, desc1, 2
2, desc2, 3
level can be anything from 1 to 7the desired outcome in the above records would be 2 rows only:
1, desc2, 2
2, desc2, 3
the filter is finding the row with the largest level number.
table1 has only one record for each no.
ideally the sub query would be included in the join statement
any ideas?
Bruce
This is a trivial operation, please post the DDL (create table) scripts for both tables, sample data as an insert statement and the expected result.
😎
November 4, 2017 at 8:10 am
There are two main approaches. Which one performs better depends on the density of table 2 with respect to table 1 and to a lesser extent on how many records there are in table 1.
Approach 1: CTE/ROW_NUMBERWITH max_level AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY t2.no ORDER BY t2.level DESC) AS rn
FROM table1 t1
INNER JOIN table2 t2
ON t1.no = t2.no
)
SELECT *
FROM max_level
WHERE rn = 1
;
Approach 2: CROSS APPLY/TOP (1)SELECT *
FROM table1 t1
CROSS APPLY
(
SELECT TOP(1) *
FROM table2 t2
WHERE t1.no = t2.no
ORDER BY t2.level DESC
) t2
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 5, 2017 at 1:28 am
Thanks Drew much appreciated. I've never worked with that code before. the initial table or combination of tables has about 13 million records unfiltered table 2 has about 6k plus so you can see the importance of not duplicating or quintupling records. I've over simplified my description and am working on that and now have something I can work with to group those records. thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply