How to get unique ros from SQL

Question

1.00/5 (2 votes)

See more:

I have a vales like this.

TaskId	InternalId	(No column name)	UploadId	RequestId	CreatedDate	ProcessStatusMappingID
959	959	0	183	959636241330158000000	3/3/2017	197
959	959	0	183	959636241463837000000	3/3/2017	197
960	960	0	183	960636241330205000000	3/3/2017	197
960	960	0	183	960636241463912000000	3/3/2017	197
961	961	0	183	961636241330219000000	3/3/2017	197
983	983	0	194	983636244258262000000	3/6/2017	197
983	983	0	194	983636244258532000000	3/6/2017	197
984	984	0	194	984636244258309000000	3/6/2017	197
984	984	0	194	984636244258553000000	3/6/2017	197
985	985	0	194	985636244258327000000	3/6/2017	197

i require a query to return the value has

TaskId	InternalId	(No column name)	UploadId	RequestId	CreatedDate	ProcessStatusMappingID
959	959	0	183	959636241463837000000	3/3/2017	197
960	960	0	183	960636241463912000000	3/3/2017	197
961	961	0	183	961636241330219000000	3/3/2017	197
983	983	0	194	983636244258532000000	3/6/2017	197
984	984	0	194	984636244258553000000	3/6/2017	197
985	985	0	194	985636244258327000000	3/6/2017	197

thanks in advnace.

What I have tried:

tried with distinct but not working.

Posted 8-May-17 21:10pm

Member 8478234

Updated 9-May-17 12:25pm

Maciej Los

v2

Add a Solution

Comments

Suvendu Shekhar Giri 9-May-17 3:33am

Anything you have tried so far?
Share the tried query.

4 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)

_Asif_ · Answer 1 · 2017-05-08T21:36:00

Solution 1

Try this,

SQL

SELECT *
FROM   TABLE T
       INNER JOIN 
       (
           SELECT TASKID, MAX(REQUESTID) RID
           FROM   TABLE T
       ) TMP ON T.TASKID = TMP.TASKID AND T.REQUESTID = TMP.RID

Posted 8-May-17 21:36pm

_Asif_

Comments

Maciej Los 9-May-17 16:41pm

Good one, even if there's no comment!

omerkamran · Answer 2 · 2017-05-08T21:52:00

Solution 2

WITH DEDUPE AS (
SELECT *
, ROW_NUMBER() OVER ( PARTITION BY TaskId ORDER BY TaskId ) AS OCCURENCE
FROM table_name
)
SELECT * FROM DEDUPE
WHERE
OCCURENCE = 1

Posted 8-May-17 21:52pm

omerkamran

Updated 8-May-17 21:53pm

v2

Maciej Los · Answer 3 · 2017-05-09T10:39:00

There's no duplicates. All rows are unique! All what you want to achieve, is to get date with the highest RequestId.

I'd suggest to use MAX() aggregate function with OVER() clause[^]:

SQL

SELECT DISTINCT TaskId,	InternalId,	NoColumnName,	UploadId,
	MAX(RequestId) OVER( PARTITION BY TaskId ORDER BY CreatedDate) AS MaxRequestId,	CreatedDate,	ProcessStatusMappingID
FROM YourTableName

Member 13154494 · Answer 4 · 2017-05-09T12:25:00

Solution 4

C#

with task as(
select  *, ROW_NUMBER() over (partition by TaskId order by TaskId) as rownum from tasks) select * from task  where rownum=1

Using CTE and concept of Row_Number we can achieve distinct records. task is name of CTE and rownum is Temporary column Name.

Posted 9-May-17 12:25pm

Member 13154494

Comments

CHill60 10-May-17 9:14am

This is exactly the same as Solution 2 - all you have done is change the alias names.

Member 13154494 10-May-17 11:07am

Yes

How to get unique ros from SQL

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us