Solved

Get Distinct rows tsql

Posted on 2013-11-05
10
551 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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