Solved

Get Distinct rows tsql

Posted on 2013-11-05
10
548 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

762 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