how to get id group by other id having rate min

Question

5.00/5 (1 vote)

See more:

I have following result

compID OrderId  rate
__________________________________________

  15	123      1000
  12	123      2344
  13	123      2456
  14	123      2666

  119	234      3245
  120	234      3678
  118	234      4566
  23	234      6789

  22	345      900
  27	345      980
  26	345      1567
  25	345      1600

now I need compid having minimum rate group by order id

for eg.

for order id 123 company id is 15
for order id 234 company id is 119
for order id 345 company id is 22

Posted 17-Aug-14 23:31pm

jagdish123061

Updated 17-Aug-14 23:37pm

v2

Add a Solution

3 solutions

Solution 2

Hi,
I think this will work for you.

SQL

select  orderid,compid from TableName
where rate in(select MIN(rate) from TableName group by OrderId)

Posted 18-Aug-14 0:16am

Sanchayeeta

Updated 18-Aug-14 0:17am

v2

Comments

Gihan Liyanage 18-Aug-14 6:35am

I didn't see your answer before post my one. Any way great, this also working.

Solution 1

SQL

select  compID ,OrderId , MIN(rate) from TableName

GROUP BY compID ,OrderId

Posted 17-Aug-14 23:40pm

Prasad Avunoori

Comments

jagdish123061 18-Aug-14 6:08am

not working it giving me all company id

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gihan Liyanage · Accepted Answer · 2014-08-18T00:33:00

Solution 3

I have Tested for you ...

SQL

SELECT compID, OrderId, rate
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY rate) AS rn
    FROM TestTable
) AS T1
WHERE rn = 1

Please mark as answered if you are ok with the answer.

Posted 18-Aug-14 0:33am

Gihan Liyanage

Comments

jagdish123061 26-Feb-15 0:28am

thank u soooo much @Gihan Liyange