Replacement of GROUP BY with ROW_NUMBER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Replacement of GROUP BY with ROW_NUMBER

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-09-01 : 02:53:52

Hello everyone,

Can I replace GROUP BY with ROW_NUMBER for the below query.


CREATE TABLE ROW_NUMBER_TEST ( ID INT, Amount1 MONEY, Amount2 MONEY)

INSERT INTO ROW_NUMBER_TEST
SELECT '1','23','42'
UNION ALL
SELECT '1','34','56'
UNION ALL
SELECT '1','45','34'
UNION ALL
SELECT '2','45','56'
UNION ALL
SELECT '2','93','78'

SELECT		ID, MAX(Amount1), MIN(Amount2) 
FROM		ROW_NUMBER_TEST
GROUP BY	ID

Vaibhav T

If I cant go back, I want to go fast...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 02:59:38

you cant as there's no guarantee that max value of Amount1 and min value of Amount2 will fall in same record for a given ID. So eventually you would require some kind of aggregation using GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 03:03:58

you can do this though but it wont be efficient as above original solution


SELECT t.ID,t1.Amount1,t2.Amount2
FROM (SELECT DISTINCT ID FROM table) t
CROSS APPLY (SELECT MAX(Amount1) AS Amount1
             FROM Table
             WHERE ID=t.ID) t1
CROSS APPLY (SELECT MIN(Amount2) AS Amount2
             FROM Table
             WHERE ID=t.ID) t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-09-01 : 03:13:44

Thanks for your quick reply visakh.

The scenario is like that
I have one existing query which returns some result set by joining some tables and group by on some columns.
But now i need to join some 2 or 3 tables more and get some columns in select list.

And I can not guarantee that after joining tables number of rows of existing result set will not affect.

Please give me some suggestion.

Vaibhav T

If I cant go back, I want to go fast...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 04:38:58

can you post some sample data and explain so that we can give you an accurate solution for your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-09-01 : 04:48:47

Will provide you soon...

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources