Please help me to search new prices of products (by sql command)

Question

0.00/5 (No votes)

See more:

Dear all,

I have a table such as:

Product_Code     UnitID     Price        Modify
=====================================================
A                CAI        3.000        22/02/2012          -> New
A                CAI        2.000        21/02/2012
A                CAI        1.000        20/02/2012

A                HOP        6.000        22/02/2012          -> New
A                HOP        5.000        21/02/2012

Please help me to select result below

Product_Code	UnitID	Price	Modify
=====================================================
A                CAI        3.000        22/02/2012
A                HOP        6.000        22/02/2012

Please help me to search new price for products
--EMAIL REMOVED--

Thanks,
SaiDon

[edit]Code blocks added - OriginalGriff[/edit]

Posted 22-Feb-12 22:13pm

DinhCongLoc

Updated 22-Feb-12 22:37pm

OriginalGriff

v3

Add a Solution

Comments

Varun Sareen 23-Feb-12 4:17am

not clear :(

DinhCongLoc 23-Feb-12 4:23am

I mean, please search products that is updated new price

DinhCongLoc 23-Feb-12 4:26am

products have same code and Unit, only change price and update's time

Orcun Iyigun 23-Feb-12 4:19am

Please do not include your email in your thread. You do not want to be scammed. do you?

DinhCongLoc 23-Feb-12 4:35am

please help me

3 solutions

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)

CRDave1988 · Answer 1 · 2012-02-22T22:58:00

Solution 1

I have not Try this but tell me what u getting:

SQL

select * from produce a
where modify = (select max(Modify) from product where productid=a.productid)

Posted 22-Feb-12 22:58pm

CRDave1988

Costica U · Answer 2 · 2012-02-22T23:30:00

This should work if Modify column is datetime

SQL

select  a.Product_Code,
        a.UnitID,
        a.Price,
        a.Modify
from    dbo.Products as a
join    (
        select  Product_Code,
                UnitID,
                max(Modify) as Modify
        from    dbo.Products
        group
        by      Product_Code,
                UnitID
        ) as b
        on b.Product_Code = a.Product_Code and b.UnitID = a.UnitID and b.Modify = a.Modify

OriginalGriff · Answer 3 · 2012-02-22T23:47:00

Solution 3

Try:

SQL

SELECT * FROM (
  SELECT Price, UnitId, [Modify],
    RANK() OVER (PARTITION BY UnitId ORDER BY [Modify] DESC) dest_rank
    FROM Products
 ) p WHERE dest_rank=1

Posted 22-Feb-12 23:47pm