Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

Get Distinct rows tsql

I have a table that I need to get distinct rows from.

id,changeDate, request,type ,status,comment

The change date includes the Time. I need to exclude Time to get distinct rows but for the final output I need to have it. Any ideas how to accomplish this.
0
JonMny
Asked:
JonMny
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
Rajkumar GsSoftware EngineerCommented:
SELECT DISTINCT id, CONVERT(VARCHAR(10), changeDate, 111) changeDate, request,type ,status,comment FROM <yourtable>

Open in new window


This will give you the disctinct with date part only.

For final output, how can we identity which record's time part need to be merged ? Not clear in your question, it seems

Raj
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Can't be done.

DISTINCT by date means each date will be a single row.
'for the final output I need to have it.' means this has to be multiple rows to show the time.

Please take a moment to revisit your requirements to make sure they are being asked correctly, then give us a before and after set of sample data to demonstrate what you're trying to pull off.
0
 
Paul_Harris_FusionCommented:
I think it depends on how you want to treat the Time value.
i.e.   if you have 2 rows with identical values of
id,changeDate, request,type ,status,comment
but different values of Time?

(and as an aside,  a column named id sounds like a key that will always have different values....are you sure it should be in the distinct column list?)

Here is one approach to give you the row with the most recent time:

Select * FROM
(
Select id,changeDate, request,type ,status,comment
, rn = ROW_NUMBER() OVER
( PARTITION BY id, CONVERT ( VARCHAR(8), changeDate, 112) ,  request,type ,status,comment
  ORDER BY changeDate DESC
)
From MyTable
) Q
where rn = 1
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
awking00Commented:
Sample data and the expected results would help us understand what you want to accomplish.
0
 
JonMnyAuthor Commented:
so this is an audit table

data would look like this

auditID,id,changeDate, request,type ,status,comment
1,1, 2013-10-07 07:13:20.207,1,1,'abc'
2,1,2013-10-07 07:13:20.207,1,2,'abc'
3,1,2013-10-07 07:13:20.210,1,2,'abc'
4,1,2013-10-07 07:13:20.216,1,4,'abc'

Open in new window


In this case I don't need  #3 the  but I do need the timestamp of the others,
0
 
awking00Commented:
Are you saying you don't need this line?
>>2,1,2013-10-07 07:13:20.207,1,2,'abc'<<

Is it because the timestamp repeats and the auditID changed to a larger value or some other reason?
0
 
JonMnyAuthor Commented:
for those two records (auditID 2 and ,3) every column is the same except the changedate (I don't use the auditID in the query)


In that case I only need one of them
0
 
awking00Commented:
>>In that case I only need one of them <<
So you need the criteria for the selection of just one. The only things that are different are the auditID and timestamp, which leaves either the min or max auditId or the min or max timestamp. Which would you choose?
0
 
awking00Commented:
I'll assume you have your own means for removing the time from changeDate (There are many ways that have been published and I have no idea which is best). In the query below substitute your method for the NoTimeChangeDate parameter.
select auditID, id, changeDate, request, type, status, comment from
(select auditID, id, changeDate, request, type, status, comment,
 row_number() over (partition by auditID, id, NoTimeChangeDate, request, type, status,  comment order by changeDate asc) rn
 from your table) as x
where x.rn = 1;
This query uses the earliest changeDate for the criteria, just change asc to desc for the latest changeDate. If you prefer to use the auditID, use it in the order by clause, again with asc or desc if you want the lowest or highest value. Hope this helps.
0
 
Paul_Harris_FusionCommented:
since auditId is different on every record you cannot use it in the partition since it will allocate rn = 1 to every row.

I think my original query is correct if we add auditId to the select part.
We partition by the date part of changeDate when generating the row number.

So this is providing the most recent row within the day.


Select * FROM
(
Select T.* ,  rn = ROW_NUMBER() OVER
( PARTITION BY id, CONVERT ( VARCHAR(8), changeDate, 112) ,  request,type ,status,comment
  ORDER BY changeDate DESC
)  
From MyTable T  
) Q
where rn = 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now