Numbering in a query - 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 2012 Forums

Transact-SQL (2012)

Numbering in a query

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2014-01-07 : 00:13:23

Hello,

I have the following logical drive data in a table:

ID, MachineName, HardDriveSize, HardDriveLetter
-----------------------------------------------
1, Test1, 2GB, C:
12, Test1, 1GB, E:
6, Test2, 1GB, C:
13, Test2, 1GB, G:
11, Test2, 1GB, F:

I need to return that data, but each row needs the first column to show that in alphabetical order C is drive 1, and E is drive 2, for example.

1, Test1, 2GB, C:
2, Test1, 1GB, E:
1, Test2, 1GB, C:
2, Test2, 1GB, F:
3, Test2, 1GB, G:

Not sure how to do the 1,2 ... 1,2, 3 above in the first column.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 00:59:15

[code]
SELECT Seq,MachineName, HardDriveSize, HardDriveLetter
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY HardDriveLetter) AS Seq,MachineName, HardDriveSize, HardDriveLetter
FROM Table
)t
ORDER BY MachineName,Seq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dcarva
Posting Yak Master

140 Posts

Posted - 2014-01-07 : 01:15:38

Thank you! I will try that as soon as I get in to work.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-07 : 01:21:27

SELECT ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY HardDriveLetter) AS ID,
MachineName,
HardDriveSize,
HardDriveLetter
FROM YourTable

veeranjaneyulu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources