get serial number beside records results

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

General SQL Server Forums

New to SQL Server Programming

get serial number beside records results

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-10-11 : 11:43:03

Hi,
I get 5 results by my sql query.

The columns are:
ID Name Address.

I want a column Serial No. to the left of ID indicating the number of row of the record.

SerialNo. ID Name Address
1
2
3
4
5

Can anyone help me?
Thanks,
Mavericky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 12:24:18

[code]
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS SerialNo,ID,Name,Address
FROM table
[/code]

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

mavericky
Posting Yak Master

117 Posts

Posted - 2011-10-11 : 19:54:51

Thanks for the reply Visakh16!!
I have one more questions:

Below I have a set of data.
Name Points Max Code
WINS 0 1 LGS
API 0 1 MGS
Store 0 1 SGS
SCHOOL 0 1 MGS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. Appt. 1 1 RS
Res. Appt. 1 1 RS
SNGL big 1 1 RS

I want to provide serial number for this data as earlier, but I want to give the same serial number for the data which have the same Name. So my data should look like this on the display:

SerialNumber Name Points Max Code
1 WINS 0 1 LGS
2 API 0 1 MGS
3 Store 0 1 SGS
4 SCHOOL 0 1 MGS
5 Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
Res. DPLX 1 1 RS
6 Res. Appt. 1 1 RS
Res. Appt. 1 1 RS
7 SNGL big 1 1 RS

Can you help me?

Thanks,
Mavericky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 01:21:06

do you mean you dont want to return serial number for duplicate name records?


SELECT CASE WHEN RN=1 THEN SerialNumber ELSE NULL END AS SerialNumber,Name, Points, Max, Code
FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY SerialNumber ORDER BY SerialNumber) AS Rn,*
  FROM
  (
    SELECT DENSE_RANK() OVER(ORDER BY Name) AS SerialNumber,Name, Points, Max, Code
    FROM Table

  )t
)m

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources