How do I get an item's rank?

Hi,

Let's say I have 10 merchants selling the same product at different prices. 
How do I get a vendor's ranking?

I'd be passing the stored procedure ProductID and VendorID. And the data 
comes from a table with three columns ProductID, VendorID, Price where the 
Price field is smallmoney.
-- 
Thanks,

Sam
0
Utf
11/29/2009 11:26:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
852 Views

Similar Articles

[PageSpeed] 20

Something like

With RankedVendors As
(Select ProductID, VendorID, Price,
   Row_Number() Over (Order By Price) As Rank
From <YourTable>
Where ProductID = <product you are looking for>)
Select ProductID, VendorID, Price, Rank
From RankedVendors
Where VendorID = <vendor id you are looking for>;

Tom

"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:41E24AD7-7300-41FA-9AC3-A242BBA2DFD9@microsoft.com...
> Hi,
>
> Let's say I have 10 merchants selling the same product at different 
> prices.
> How do I get a vendor's ranking?
>
> I'd be passing the stored procedure ProductID and VendorID. And the data
> comes from a table with three columns ProductID, VendorID, Price where the
> Price field is smallmoney.
> -- 
> Thanks,
>
> Sam 

0
Tom
11/29/2009 11:42:21 PM
When I first read this, I assumed you wanted the vendor selling the most, 
only sales aren't available.

Therefore, based on the data available, you must want them ranked by how 
much each vendor is selling an item for.

SELECT ProductID, VendorID, Price
FROM Table
WHERE ProductID = @ProductID
AND VendorID = @VendorID
ORDER BY Price Desc

"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:41E24AD7-7300-41FA-9AC3-A242BBA2DFD9@microsoft.com...
> Hi,
>
> Let's say I have 10 merchants selling the same product at different 
> prices.
> How do I get a vendor's ranking?
>
> I'd be passing the stored procedure ProductID and VendorID. And the data
> comes from a table with three columns ProductID, VendorID, Price where the
> Price field is smallmoney.
> -- 
> Thanks,
>
> Sam 


0
Jay
11/29/2009 11:58:20 PM
What's your business rule for the "ranking" of vendors?  This sounds a lot 
like your previous question.

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:41E24AD7-7300-41FA-9AC3-A242BBA2DFD9@microsoft.com...
> Hi,
>
> Let's say I have 10 merchants selling the same product at different 
> prices.
> How do I get a vendor's ranking?
>
> I'd be passing the stored procedure ProductID and VendorID. And the data
> comes from a table with three columns ProductID, VendorID, Price where the
> Price field is smallmoney.
> -- 
> Thanks,
>
> Sam 

0
Michael
11/30/2009 1:52:23 AM
Come on Michael, we all know this is a school assignment.

"Michael Coles" <admin@geocodenet.com> wrote in message 
news:65104DC6-1C75-4A9C-893F-6B10290C3143@microsoft.com...
> What's your business rule for the "ranking" of vendors?  This sounds a lot 
> like your previous question.
>
> -- 
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption" 
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "Sam" <Sam@discussions.microsoft.com> wrote in message 
> news:41E24AD7-7300-41FA-9AC3-A242BBA2DFD9@microsoft.com...
>> Hi,
>>
>> Let's say I have 10 merchants selling the same product at different 
>> prices.
>> How do I get a vendor's ranking?
>>
>> I'd be passing the stored procedure ProductID and VendorID. And the data
>> comes from a table with three columns ProductID, VendorID, Price where 
>> the
>> Price field is smallmoney.
>> -- 
>> Thanks,
>>
>> Sam
> 


0
Jay
11/30/2009 2:29:33 AM
Probably.  I just wanted to see if he recognized that he already asked the 
same basic question and already got an answer. :)

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Jay" <spam@nospam.org> wrote in message 
news:uVDaxTWcKHA.6096@TK2MSFTNGP02.phx.gbl...
> Come on Michael, we all know this is a school assignment.
>
> "Michael Coles" <admin@geocodenet.com> wrote in message 
> news:65104DC6-1C75-4A9C-893F-6B10290C3143@microsoft.com...
>> What's your business rule for the "ranking" of vendors?  This sounds a 
>> lot like your previous question.
>>
>> -- 
>> Thanks
>>
>> Michael Coles
>> SQL Server MVP
>> Author, "Expert SQL Server 2008 Encryption" 
>> (http://www.apress.com/book/view/1430224649)
>> ----------------
>>
>> "Sam" <Sam@discussions.microsoft.com> wrote in message 
>> news:41E24AD7-7300-41FA-9AC3-A242BBA2DFD9@microsoft.com...
>>> Hi,
>>>
>>> Let's say I have 10 merchants selling the same product at different 
>>> prices.
>>> How do I get a vendor's ranking?
>>>
>>> I'd be passing the stored procedure ProductID and VendorID. And the data
>>> comes from a table with three columns ProductID, VendorID, Price where 
>>> the
>>> Price field is smallmoney.
>>> -- 
>>> Thanks,
>>>
>>> Sam
>>
>
> 

0
Michael
11/30/2009 5:36:57 AM
Thank you all for your responses. Sorry for not making it clear.

Ranking in terms of "price". So if Vendor A sells the item for 19.99 and B 
sells it for 21.99, etc. I want to send ProductID and VendorID and get 1 for 
Vendor A as he's got the lowest price so he's ranked #1 in price.
-- 
Thanks,

Sam


"Michael Coles" wrote:

> Probably.  I just wanted to see if he recognized that he already asked the 
> same basic question and already got an answer. :)
> 
> -- 
> Thanks
> 
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption" 
> (http://www.apress.com/book/view/1430224649)
> ----------------
> 
> "Jay" <spam@nospam.org> wrote in message 
> news:uVDaxTWcKHA.6096@TK2MSFTNGP02.phx.gbl...
> > Come on Michael, we all know this is a school assignment.
> >
> > "Michael Coles" <admin@geocodenet.com> wrote in message 
> > news:65104DC6-1C75-4A9C-893F-6B10290C3143@microsoft.com...
> >> What's your business rule for the "ranking" of vendors?  This sounds a 
> >> lot like your previous question.
> >>
> >> -- 
> >> Thanks
> >>
> >> Michael Coles
> >> SQL Server MVP
> >> Author, "Expert SQL Server 2008 Encryption" 
> >> (http://www.apress.com/book/view/1430224649)
> >> ----------------
> >>
> >> "Sam" <Sam@discussions.microsoft.com> wrote in message 
> >> news:41E24AD7-7300-41FA-9AC3-A242BBA2DFD9@microsoft.com...
> >>> Hi,
> >>>
> >>> Let's say I have 10 merchants selling the same product at different 
> >>> prices.
> >>> How do I get a vendor's ranking?
> >>>
> >>> I'd be passing the stored procedure ProductID and VendorID. And the data
> >>> comes from a table with three columns ProductID, VendorID, Price where 
> >>> the
> >>> Price field is smallmoney.
> >>> -- 
> >>> Thanks,
> >>>
> >>> Sam
> >>
> >
> > 
> 
0
Utf
11/30/2009 1:50:01 PM
Reply:

Similar Artilces:

File not getting deleted.
Hi, In my application I am downloading a files with the help curllib library. After downloading I am calculating the MD5 of for the file and checking it with MD5 I am having if that is not getting matchaed then I am deleting the file. Now I am facing a problem that, with certain files when downloaded and MD5 is not getting matched they are not getting deleted. after download I am closing the file with fclose() (it returns success in all the cases) and then deleting with SHFileOperation() api. With cases where file is not getting deleted my observation was fclose() was taking 5-6 second...

why do inbox items disappear, although they are still there
Messages in my inbox after a few days may disappear, they can be found by a search, which says they are in my inbox, i can move them to any other subdirectory, but not back to my inbox where i want them. Check your View settings and change it to All Messages, not just unread ones or those younger than XX number of days. You should not use your Inbox for storage. Make a subfolder of the Inbox to save items. "hms" wrote: > Messages in my inbox after a few days may disappear, they can be found by a > search, which says they are in my inbox, i can move them to any other &g...

Getting started tabs
I installed the getting started tab in MS Office 2007 and it does not show up when I open word . The same for Powerpoint and Excel. Any ideas? See the "Important before you download:" information at http://www.microsoft.com/downloads/details.aspx?FamilyID=F587370C-FDAE-4EDE-B528-AC58031A5DFF&displaylang=en -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "Larry" <Larry@discussions.microsoft.com> wrote in message news:C73D97D3-F091-4F5F-AB24-B...

Best way to set up Query to get a percentage
I have a "classified" field that is a yes/no check box to determine if a supplier has been classified or not. I need to get the sum of suppliers that are and are not classified and then I need to get a percentage of suppliers who is and who isn't classified from the total suppliers. I also want to be able to chart this without exporting to Excel. Any ideas on the best way to set this up? Marc - You can do that in a query. Change the fields and table names to be yours: SELECT Sum(IIf([Classifed],1,0)) AS Yes_Count, Count(SupplierID) AS Total_Count, Sum(IIf([C...

How to get back deleted mails?
hi I've deleted mails in my Outlook, and someone else has emptied my outlook mail garbage. Is there any way to get those mails back? Or maybe a tool to scan the disk and getback all the deleted files? Or any other solution. Thanks http://www.recovermyfiles.com/Recover_deleted_email.html http://www.iolo.com/sr/tool.cfm?tool=22 "mastergoddot" <mastergoddot@free.fr> wrote in message news:0c1d01c3a214$dc2258f0$a401280a@phx.gbl... > hi > > I've deleted mails in my Outlook, and someone else has > emptied my outlook mail garbage. > > Is there any way ...

vbscript to get photos from ldap provider
Hi All.... I'm trying to connect to an ldap provider and pull user photos and save them to file. The vbscript i'm using runs fine (kinda sorta). It does connect and pull the photos out and saves them to file, but it only pulls the first 500 users and then I get this error "Provider: The size limit for this request was exceeded." I've tried playing around with the provider and page size to no avail. Thanks in advance for any suggestions or advice! ################################################################### ' Set path to save picture slocal=&quo...

new item process
I have 2 stores and am running HQ and SO. Currently, when I receive an item not in my database (I've been in business 2 years, so my database is still growing!) I have to 1. Create the item 2. create a worksheet to send the items to the stores' databases 3. scan the item again to put it into the purchase order in order to get the items into the database. That's 3 steps; when I have a lot of new items (which is often) I spend a LOT of time on this process. I would LOVE to see a way to streamline/integrate this process. Maybe I'm doing something wrong? -- Jennifer -...

? CListCtrl::GetColumn To Get Column Name
Hi, I've written an app that's kind of like WinSpy/Spy++ to make adjustments to list controls. It can correctly set an app's list control's modes, alignments, sorting, styles, and extended styles. It can also get the widths of the columns and count of items. The problem is that I cannot seem to get the columns' names. My app uses code like this, where lc is a pointer to the target app's list control: . lc->GetColumnWidth(.); lc->GetItemCount(.); lc->ModifyStyle(.); lc->SetExtendedStyle(.); lc->GetHeaderCtrl()->GetItemCount(); . That a...

"Get/Change" Error message Safe Pay
We recently completed upgrading a customer from version 8 to version 10. We get the following error message when we reload transactions in safe pay. "A get/change first operation on table "ME_Void_Checks" failed accessing SQL data. Any help appreciated. Thanks Russ ...

Money 2005
I need to know the proceedure for getting my money back for Money 2005 Premium. Also, will MS delete ALL of my financial information from its servers? Thanks. MS will do nothing to delete your data. It'll probably be there 100 years from now unless you tell Money to delete it. Do this by going into the settings and clearing the setting for Sync with the web--or whatever they call it. I'd look, but I Just Said No to M05 already. See http://umpmfaq.info/faqdb.php?q=49 for the 30DMBG question. BTW, the Money development team swears Most Users Prefer all of this stuff. You must ...

CRM 3.0 for current customer...when do I get this?
I have an active service agreement, but still haven't received CRM 3.0, even though I've read here that it shipped December 6. Also, it's not available for download from Customer Source and we're still listed as being licensed for 1.2 only. Can anyone please explain the process for acquiring 3.0? Thank you. It depends on the type of Software Assurance you own (Enterprise licensees will receive the upgrade license and media automatically), but most SA licensees will have to ask their MBS reseller to order the license keys and media, for a nominal fee (shipping and hand...

Items being run up are added to PO created on other station
Why are items that are being added to a sale apearing in the Purchase order being created on another computer? I have hotfix #9 (2.0.0115) installed. :;pud ...

Getting a Pointer to CFrameWnd
In an SDI application, is there a convenient method to access a pointer to the current Frame Window from within the context of CWinApp? Thanks. > In an SDI application, is there a convenient method to access a pointer to > the current Frame Window from within the context of CWinApp? Thanks. AfxGetMainWnd() --- Ajay Why sure! That'l get it from just about anywhere. Thanks Ajay! "Ajay Kalra" wrote: > > In an SDI application, is there a convenient method to access a pointer to > > the current Frame Window from within the context of CWinApp? Thanks. > &...

"Insert Comment" macro--can't get rid of reviewing pane
I just recently got a new laptop and decided to try Office 2010 while I'm at it. I have a single, simple macro recorded with a key-combo saved to it that will insert a comment. I edit documents and spend MANY hours inserting comments. My macro is simple: Insert Comment. That's it. I just do Alt-W and voila! The comment is inserted, ready for me to type my text into it. In Word 2003, it inserts nicely and neatly. However, in trying to create this same macro in Word 2010, when I actually go to run the macro, it ALWAYS opens up the reviewing pane at the bottom!! And ...

Find all item codes for all items under one category
I have a list of thousands of UPCs with the category name in adjacent cells. I need to build a formula to automatically populate a list of UPCs based on the category selected in another worksheet in the workbook. The category selection is already automatically selected from data that is imported in another worksheet in the workbook. This seems like it should be very simple, but I can't find anything online that provides a simple way to do it. I only find manual ways to do it. Thanks for any help out! Kevin K If desired, send your file to my address below. I will...

user is having a problem getting new email
this started happen recently to one user...when everytime a new email arrives...she wont see it on her inbox until to scroll up or down in outlook or browse to another folders like deleted, sent, etc. i have already reinstall ms office for her and deleted her outlook profiles...but problem still exist. anyone know how to fix this problem? thanks in advance. That means that something is blocking UDP packets from the workstation to the Exchange server. Exchange uses UDP for new mail notifications. Check for client firewall (XP SP2?), or a firewall between the client and server. If i...

Re: calculate ranking
i am trying to make a racing form in exel to calculate race results th problem lies in the ex equo rule theat if 2 or more members have th same end result , first the highist known scored points between the make that member rank higer than the other so 1 person has 8-6-2= 1 pts second has 8-4-4- =16 points but member 1 has priorety becouse end 8 is equal but result 6 is over 4 points higher ca anyone help m with an vb formula or other solution to sort them in good order? thank -- Message posted from http://www.ExcelForum.com bertmidd < wrote: > i am trying to make a racing form in e...

RPC over HTTP and this is what i get...
Hi all I have exc2003 SP2 and are trying to implement RPC over HTTP to use with laptops. I'm testing it on mine first and this is what i get. Firstly i used this doc http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm. i Have winxp pro and outlook2003. When trying to connect via my wireless card i type outlook.exe /rpcdiag in the run box and if i look at the connection dialogue box it shows the following: Servername = the name we use on our network, Type = Directory/Mail, Connection = -- (it physical show the 2 lines --), Status = Connecting. It stays there fo...

every time i use MSOutlook i get an error report wheni close it
just upgraded to Windows XP now i keep getting error signs and also have something called Photo Gallery tring to install .it ask's for a disc? Help Please Try making a new profile in outlook. No idea on the other problem - we only know outlook here. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mai...

How to get Recurring Appointment dates with VBA?
I need to loop through all future Outlook appointment items and populate a table with the date and subject of each appointment. The problem is with recurring apointments - how do I get the recurring dates? If the appointment is NOT recurring, this works: Dim ola As Outlook.AppointmentItem For Each ola In olns.GetDefaultFolder(olFolderCalendar).Items If ola.Start > Date Then myDate = ola.Start mySubject = ola.Subject End If But the above code will not return recurring appointments. I've tried this, but no luck: If ola.IsRecurring Then Dim varItem A...

Sent Items appearing in Inbox
I am a consultant who has a client with the following problem (and I'm stumped) Outlook 2003 (caching mode), connected to SBS 2003 (and Exchange) and every item he sends (from either his laptop or his desktop) appears in both his inbox and his sent items folder. I've tried deleting and re-creating his profile, but didn't think that would help, since it seems to happen on different computers also. Unfortunately, he is two hours away, so I won't be able to try any recommendations right away, but does anybody have any idea what I should be looking for? Could it be something in...

using extended mapi to create a pst file and add items to it #2
Hi, We're migrating users from a proprietary mail system to Exchange and hence We'd like to import the existing messages into .pst files, so the users can access them from Outlook once they're on Exchange. Now, I can't find anything about how to create a .pst file and add email items into it using extended mapi. Can anybody list the simple roadmap for me? Any urls to code samples will be very helpful. In addition, any suggestions of a simple and fast way to do the pst part other that using mapi in c++? aparna ...

Do I have to leave Outlook open to get my mail?
I just switced to Microsoft Outlook. I was wondering if there was anyway for me to close MO and still recieve alerts that I have a new email. Thank you! Mrs_Mc <Mrs_Mc@discussions.microsoft.com> wrote: > I just switced to Microsoft Outlook. I was wondering if there was > anyway for me to close MO and still recieve alerts that I have a new > email. Not without some kind of add-in. Outlook must be running to get mail reception alerts, no different than, say, Outlook Express. -- Brian Tillman "Brian Tillman" wrote: > Mrs_Mc <Mrs_Mc@discussions.microsoft...

Custom scroll bars, buttons and other typical items
I am working on a small game right now and am programming a windows GUI right now, since it has been a normal console game which isn't really attractive. I will of course be using buttons of all sorts and a text window with scroll bars. What I am wondering is: I see those items in many games, but not in the usual windows style. Are those customized versions of the items the windows functions and classes already provide or are those completely hand-made? If customized, then how is that done? I am not just talking about e.g. changing the colors, but changing the entire design. If completely...

How to get text wrap to work with datetime data?
I would like to get a numeric timestamp value (date + time) to display as wrapped text (2 lines) in a single cell with the date on the first line and the time on the second line: A 1 Timestamp 12/17/10 2 6:22 am 3 I tried entering the data as a number (ctrl+; ctrl+:) and applying a custom format (mm/dd/yy hh:mm am/pm). This formats correctly as long as the column is wide enough, but if the column is too narrow, I get ###### and setting text wrap doesn't work even if the row is double depth. I tried entering the timestamp in two parts separated by Alt+Enter. [...