please help me to create a sql query

Question

i have following 3 tables

CREATE TABLE [dbo].[dspartner](
[dspartnerid] [bigint] IDENTITY(1,1) NOT NULL,
[name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[priority] [int] NULL)

CREATE TABLE [dbo].[hotels](
[hotelid] [int] PRIMARY KEY  IDENTITY(1,1) NOT NULL,
[name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DSPartnerid] [bigint] NULL,
)

CREATE TABLE [HotelSourceMap](
[hotelsourcemapid] [bigint] PRIMARY KEY  IDENTITY(1,1) NOT NULL,
[dspartnerid] [bigint] NOT NULL,
[hotelid] [bigint] NOT NULL,
    [dshotelid] bigint
)

situation is like this:

i want to table:dspartner contain details about datasource partner. table:hotels contain details of hotels table:HotelSourceMap contain record of hotels.hotelid, dsparnter.dspartnerid

new i want to update hotels table so that set hotels.dspartner = hotelsourcemap.dspartnerid where hotels.hotelid = hotelsourcemap.hotelid and hotelsourcemap.dspartnerid = dspartner.dspartnerid (where dspartnerid's priorirty is high)

Note:-priority contain int value and the max int value will be considerd as max priority. if two dspartner have same priority then get any one of them

sql server 2005

Answer 1

Use:

UPDATE HOTELS
   SET dspartners = (SELECT x.dspartnerid
                       FROM (SELECT hsm.dspartnerid,
                                    ROW_NUMBER() OVER(PARTITION BY hsm.hotelid 
                                                      ORDER BY p.priority, p.name DESC) AS rank
                               FROM HOTELSOURCEMAP hsm
                               JOIN DSPARTNER p ON p.dspartnerid = hsm.dspartnerid
                              WHERE hsm.hotelid = hotelid) x
                      WHERE x.rank = 1)

To make sure things are working properly, run:

SELECT x.dspartnerid
  FROM (SELECT hsm.dspartnerid,
               ROW_NUMBER() OVER(PARTITION BY hsm.hotelid 
                                 ORDER BY p.priority, p.name DESC) AS rank
          FROM HOTELSOURCEMAP hsm
          JOIN DSPARTNER p ON p.dspartnerid = hsm.dspartnerid
         WHERE hsm.hotelid = ?) x
 WHERE x.rank = 1

Replace the ? with any hotelid of your choice.
It will only return one dspartnerid value, even if there were more than one dspartnerid's with the same high priority.