Get Distinct column value rows

Question

0.00/5 (No votes)

See more:

SQL-server-2000

Hello All,

I have one table with columns and values like below

stgID--CMD---KitProductID---KitComponentID--KitQty created_On updated_On
14-----Insert-26532-00-0131--26532-00-0123--123 ---2013-05-01 18:50:50.447 2013-05-01 18:50:50.430
15-----UPDATE-26532-00-0131--26532-00-0123--123 ---2013-05-01 18:51:50.447 2013-05-01 18:50:50.430
16-----Insert-----26532-00-0131--26532-00-0123--122 ---2013-05-01 18:52:50.447 2013-05-01 18:50:50.430
17-----UPDATE-26532-00-0131--26532-00-0123--122 ---2013-05-01 18:53:50.447 2013-05-01 18:50:50.430

In this data I have KitProductId and for that ProductId having kit components. ProductID is same for all the Kitcomponents. Here Iam having multiple records for same componentId . I need to get only one record for each componentid i.e latest record based on createdon.

Output should be like

stgID--CMD---KitProductID---KitComponentID--KitQty created_On updated_On
15-----UPDATE-26532-00-0131--26532-00-0123--123 ---2013-05-01 18:51:50.447 2013-05-01 18:50:50.430
17-----UPDATE-26532-00-0131--26532-00-0123--122 ---2013-05-01 18:53:50.447 2013-05-01 18:50:50.430

Please help me on this. I have tried distinct and all but not helped.

Thanks in advance.

Naveen.

Posted 2-May-13 1:58am

NaVeN Kumar

Add a Solution

1 solution

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)

Aarti Meswania · Accepted Answer · 2013-05-02T02:12:00

Solution 1

SQL

select * from 
(
    select Row_Number() over(Partition by KitComponentID,Created_on order by KitComponentID,Created_On desc) as SrNo, * 
    from tblnm 
) as tmptbl 
where SrNo=1

for sql 2000

SQL

select * 
from tblnm t1 where t1.stgID = (select top 1 stgid from tblnm t2 where t2.KitComponentID = t1.KitComponentID order by Created_On desc)

Happy Coding!
:)

Posted 2-May-13 2:12am

Aarti Meswania

Updated 2-May-13 3:31am

v3

Comments

NaVeN Kumar 2-May-13 8:19am

Sorry... The CMD may have another value like 'Insert', 'Delete'... I cant get on cmd base. I have to get latest record for that component id based on created_on or Updated_on.

Aarti Meswania 2-May-13 8:29am

check updated solution

NaVeN Kumar 2-May-13 9:20am

Row_Number() is not working as it is sql2000. Giving exception like below
'Row_Number' is not a recognized function name.

Aarti Meswania 2-May-13 9:31am

check updated solution

NaVeN Kumar 3-May-13 0:56am

Your answer is really awsomeeeeeeeeeeeee........ Thank you very much for your help on this... :)

Aarti Meswania 3-May-13 1:03am

Welcome!
Glad to help you! :)