Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get Distinct rows tsql

Posted on 2013-11-05
10
Medium Priority
?
552 Views
Last Modified: 2013-11-07
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
Comment
Question by:JonMny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39624613
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39624630
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
 
LVL 12

Assisted Solution

by:Paul_Harris_Fusion
Paul_Harris_Fusion earned 1336 total points
ID: 39624642
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 32

Expert Comment

by:awking00
ID: 39624845
Sample data and the expected results would help us understand what you want to accomplish.
0
 
LVL 9

Author Comment

by:JonMny
ID: 39624933
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
 
LVL 32

Expert Comment

by:awking00
ID: 39625087
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
 
LVL 9

Author Comment

by:JonMny
ID: 39625105
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
 
LVL 32

Expert Comment

by:awking00
ID: 39625185
>>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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 664 total points
ID: 39625271
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
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 1336 total points
ID: 39626772
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

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question