Rank based on amount desc and date asc

[pritesh2010]

REATE TABLE [dbo].[BiddingTable](
    [cAuctionId] [int] NULL,
    [idUserId] [int] NULL,
    [cItemId] [int] NULL,
    [vcBidamount] [nvarchar](50) NULL,
    [vcBidcount] [int] NULL,
    [bidQty] [varchar](5) NULL,
    [vcBidtime] [datetime] NULL,
    [status] [varchar](50) NULL
) ON [PRIMARY]




INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 36, 21, N'93.6', 1, N'0', CAST(0x0000A05500F83B44 AS DateTime), N'Accepted')
INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 5, 21, N'93.7', 4, N'0', CAST(0x0000A055010411BC AS DateTime), N'Accepted')
INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 36, 21, N'93.8', 5, N'0', CAST(0x0000A05501086D5C AS DateTime), N'Accepted')
INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 5, 21, N'93.8', 6, N'0', CAST(0x0000A055010ABD28 AS DateTime), N'Accepted')
INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 36, 21, N'93.9', 7, N'0', CAST(0x0000A055010AF7C0 AS DateTime), N'Accepted')
INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 5, 21, N'93.9', 8, N'0', CAST(0x0000A05600BF8614 AS DateTime), N'Accepted')
INSERT [dbo].[BiddingTable] ([cAuctionId], [idUserId], [cItemId], [vcBidamount], [vcBidcount], [bidQty], [vcBidtime], [status]) VALUES (10, 36, 21, N'94', 9, N'0', CAST(0x0000A05600C0231C AS DateTime), N'Accepted')




I want result as below




Assign Highest Rank of based on Max BidAmount.


if Bid Amount gets tie with other user then who ever enter bid amount first he will get the high Rank..Mean based on Bidtime Rank will assign. 

[darkagn]

You can use the RANK function to do this. Here is a link to MSDN's description of the function, and here is an excellent article that has some examples of its use.

Something like this will work for you, you may need to change the order to get the way you want it:

SELECT RANK() OVER (ORDER BY vcBidAmount, vcBidTime DESC) AS Ranking,
*
FROM BiddingTable

As an aside, it is not good to use VARCHAR fields for amounts of money, the sorting will be strange and it isn't truly representative of what type of data you are deailing with. For example, '100.3' comes before '2.7' in a string comparison, but obviously the number 100.3 > 2.7.

[poojavb]

The above query will show BidTime as Desc and BidAmount as Asc....

But according to the criteria

1. Assign Highest Rank of based on Max BidAmount.

2. If Bid Amount gets tie with other user then who ever enter bid amount first he will get the high Rank

So just use the opposite of what darkagn as given

SELECT RANK() OVER (ORDER BY vcBidAmount DESC, vcBidTime) AS Ranking,
*
FROM BiddingTable

Hope this is what pritesh2010 wants....