Solved

Get Distinct rows tsql

Posted on 2013-11-05
10
536 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
  • 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 65

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 334 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
 
LVL 31

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

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 31

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 31

Assisted Solution

by:awking00
awking00 earned 166 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 334 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now