Remove repeating data in query....

[pwynne33]

I've got a query that displays

ID No - Date1 - Date2 - Diff from Date1 to Date2 - Date3 - Diff from Date1 to Date3

However for each ID No there are more than one record in this query.

I'd like to just show the first record for each ID No instead of displaying all the records with that ID no.

On another forum this was suggested....

Code:
select [ID No]
, min([Date1])
, min([Date2])
, min([Date1])
-min([Date2]) as [Diff from Date1 to Date2]
, min([Date1])
-min([Date3]) as [Diff from Date1 to Date3]
from yourtable
group
by [ID No]

But that seems to just order them by the min of date diff 1, where as I just want to display the first record of that set...

For example, with the code above it shows:

ID - date 1 - date 2 - datediff(date1,date2) - date 3 - datediff(date1,date3)

1-nn/nn/nnnn-nn/nn/nnnn- 10 - nn/nn/nnnn/ - ##
1-nn/nn/nnnn-nn/nn/nnnn- 20 - nn/nn/nnnn/ - ##
1-nn/nn/nnnn-nn/nn/nnnn- 30 - nn/nn/nnnn/ - ##
5-nn/nn/nnnn-nn/nn/nnnn- 6 - nn/nn/nnnn/ - ##
5-nn/nn/nnnn-nn/nn/nnnn- 8 - nn/nn/nnnn/ - ##
5-nn/nn/nnnn-nn/nn/nnnn- 10 - nn/nn/nnnn/ - ##

but I would like it to show...

1-nn/nn/nnnn-nn/nn/nnnn- 10 - nn/nn/nnnn/ - ##
5-nn/nn/nnnn-nn/nn/nnnn- 6 - nn/nn/nnnn/ - ##

Any ideas?

Thanks.

 

[PHV]

I just want to display the first record
What is the sequencer ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pwynne33]

Sequencer? (Sorry if I sound dumb)

 

[PHV]

How do you know which is the first, wich the second, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pwynne33]

Its the smallest from the date difference between date1 and date3.

Thanks.

 

[PHV]

Something like this ?
SELECT A.[ID No], A.date1, A.date2, A.Date2-A.Date1 AS N1_2, A.date3, A.Date3-A.Date1 AS N1_3
FROM yourTable AS A INNER JOIN
(SELECT [ID No], Min(Date2-Date1) As MinDiff FROM yourTable GROUP BY [ID No]) AS B
ON (A.[ID No]=B.[ID No]) AND ((A.Date2-A.Date1)=B.MinDiff)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244