Discontinuous incremental number

Question

Discontinuous incremental number

SMNayak

Ten Centuries

Points: 1214
More actions
August 23, 2010 at 1:27 am

#72056

I have a query which returns the result like below:
hotel_idhotel_namefacilities
456BASIL IKONBREAKFAST
456BASIL IKONNEWSPAPER
709ROYAL ORCHID SUITESHEALTH FACILITY
709ROYAL ORCHID SUITESCABLE
709ROYAL ORCHID SUITESFULL BREAKFAST
709ROYAL ORCHID SUITESBRAODBAND
In order to continue my processing, I need one additional incremental column. But the condition is that the new column should restart the numbering for every hotel.
Finally, my result should look like this:
hotel_idhotel_nameidfacilities
456BASIL IKON1BREAKFAST
456BASIL IKON2NEWSPAPER
709ROYAL ORCHID SUITES1HEALTH FACILITY
709ROYAL ORCHID SUITES2CABLE
709ROYAL ORCHID SUITES3FULL BREAKFAST
709ROYAL ORCHID SUITES4BRAODBAND
The business rule is that, any hotel can have a maximum of 44 facilties
Any help appreciated...
Regards,
Nayak

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Nayak

You've posted in the SQL Server 7,2000 forum. Please will you confirm that you are indeed using one of those platforms? If you're using SQL Server 2005 or above, it's much easier - you can use the ROW_NUMBER() statement.

By the way, you're more likely to get people to help you if you provide table DDL, sample data and your existing query. Not only that, you'll also get a tested solution.

John

SMNayak Ten Centuries Points: 1214 More actions · Answer 2

Thanks John...

I am using SQL 2000.

Here is the script which will generate sample source resultset:

IF OBJECT_ID('Hotel') IS NOT NULL

DROP TABLE Hotel

CREATE TABLE dbo.Hotel(

hotel_id INT,

hotel_name VARCHAR(100),

facilities VARCHAR(100)

)

GO

INSERT INTO Hotel

SELECT 456,'BASIL IKON', 'BREAKFAST'

UNION SELECT 456,'BASIL IKON', 'NEWSPAPER'

UNION SELECT 709,'ROYAL ORCHID SUITES', 'HEALTH FACILITY'

UNION SELECT 709,'ROYAL ORCHID SUITES', 'CABLE'

UNION SELECT 709,'ROYAL ORCHID SUITES', 'FULL BREAKFAST'

UNION SELECT 709,'ROYAL ORCHID SUITES', 'BRAODBAND'

GO

SELECT * FROM Hotel

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 3

You can use ROW_NUMBER() function which will provide rankings for data based on columns. Use row_number() on Hotel_id column.No need of adding new column. check this query

SELECT hotel_id ,hotel_name + ' '+ CAST((ROW_NUMBER()OVER (partition by hotel_id ORDER BY hotel_id)) AS VARCHAR(3))AS hotel_name, facilities

FROM hotel

Thanks,

Amit kulkarni

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

Nayak

Try this

CREATE TABLE #Sequence (

-- Don't include hotel_name because might affect performance

-- inserting these values if you have a very large table.

-- You will want to test that this benefit is not offset

-- by the cost of the join at the end

sequence int IDENTITY(1,1)

,hotel_id int

,facilities varchar(100)

)

INSERT INTO #Sequence (hotel_id, facilities)

SELECT hotel_id, facilities

FROM dbo.Hotel

ORDER BY hotel_id

-- ORDER BY guarantees that identity column will number

-- the rows in the correct sequence.

-- See http://support.microsoft.com/kb/273586

-- Join back to the main table to get results

SELECT

h.hotel_id

,h.hotel_name

,s.sequence - m.sequence + 1

,h.facilities

FROM

dbo.Hotel h

JOIN

#Sequence s

ON

h.hotel_id = s.hotel_id AND h.facilities = s.facilities

JOIN ( -- Get min sequence number for each hotel

SELECT hotel_id, MIN(sequence) as sequence

FROM #Sequence

GROUP BY hotel_id

) m

ON

h.hotel_id = m.hotel_id

John

SMNayak Ten Centuries Points: 1214 More actions · Answer 5

SMNayak

Ten Centuries

Points: 1214

August 23, 2010 at 4:09 am

#1210327

Thanks John and Amit..

That helped a lot...