Row_Number!!! - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Row_Number!!!

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 15:13:04

Hi,
This is my view in Sql2005
SELECT DISTINCT VLAN8, 'CONV' AS VLEDUS, MAX(CAST(VLEDBT AS int)) AS MaxVLEDBT
FROM JDE_DEVELOPMENT.TESTDTA.F06116Z1
GROUP BY VLAN8, VLEDUS

2111 CONV 2111
4803 CONV 31
7550 CONV 33

When I add row number function the results become wrong:

SELECT DISTINCT VLAN8, 'CONV' AS VLEDUS, ROW_NUMBER() OVER (ORDER BY VLAN8) AS VLEDLN, MAX(CAST(VLEDBT AS int)) AS MaxVLEDBT
FROM JDE_DEVELOPMENT.TESTDTA.F06116Z1
GROUP BY VLAN8, VLEDUS, VLEDLN

2111 CONV 1 2111
4803 CONV 2 31
4803 CONV 3 31
4803 CONV 4 31
4803 CONV 5 31
4803 CONV 6 31
4803 CONV 7 31
4803 CONV 8 31
4803 CONV 9 31
4803 CONV 10 31
4803 CONV 11 31
4803 CONV 12 31
7550 CONV 13 33
7550 CONV 14 33
7550 CONV 15 33
7550 CONV 16 33

I just need to have :
2111 CONV 1 2111
4803 CONV 2 31
7550 CONV 3 33

Please help me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 15:19:54

Why do you have DISTINCT in your view? The grouping takes care of it already, right?

For the rownumber thing, add the code to partition it. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 15:25:53

sorry Tara,I don't understand.
How should I add the code to partition?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 15:50:17

BOL stands for SQL Server Books Online, which is the documentation for SQL Server.

Why did you add VLEDLN to your GROUP BY? Do not add columns to it just to avoid an error. By adding columns, you are changing what will get returned.

To fix the issue, you can either use ROW_NUMBER() function when calling the view or add a derived table inside the view.

Outside the view:
SELECT VLAN8, VLEDUS, MaxVLEDBT, ROW_NUMBER() OVER (ORDER BY VLAN8) AS VLEDLN
FROM YourView

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 15:58:45

Thanks Tara.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-05-29 : 04:04:17

Actually, I don't understand your original query.

SELECT DISTINCT VLAN8, 'CONV' AS VLEDUS, MAX(CAST(VLEDBT AS int)) AS MaxVLEDBT
FROM JDE_DEVELOPMENT.TESTDTA.F06116Z1
GROUP BY VLAN8, VLEDUS

GROUP BY doesn't see the column names in the SELECT part, just the columns in the FROM, so for this to be a valid query there would have to be a column VLEDUS in JDE_DEVELOPMENT.TESTDTA.F06116Z1. It seems unlikely that this is what you would want since you then have a MAX value that is dependent on a value of JDE_DEVELOPMENT.TESTDTA.F06116Z1.VLEDUS that you can't see in the output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 04:19:44

Yeah.thats true. You cant use alias in GROUP BY.However you could put the main query inside a derived table and then do group by. like this
SELECT DISTINCT t.VLAN8, t.VLEDUS, t.MaxVLEDBT
FROM
(SELECT VLAN8, 'CONV' AS VLEDUS, MAX(CAST(VLEDBT AS int)) AS MaxVLEDBT
FROM JDE_DEVELOPMENT.TESTDTA.F06116Z1)t
GROUP BY t.VLAN8, t.VLEDUS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources