How can we select distinct *, From and get only Distinct IDs?

Question

I have a small dataset that looks like this.

SELECT *
INTO Order_Table
FROM (VALUES
   (1,          456,       'repair',       'House'),
   (2,          456,        'paint',       'House'),
   (3,          678,        'repair',      'Fence'),
   (4,          789,        'repair',      'House'),
   (5,          789,        'paint',       'House'),
   (6,          789,        'repair',      'Fence'),
   (7,          789,        'paint',       'Fence')
   )
v (OrderNum,    CustomerNum, OrderDesc,   Structure)


SELECT *
INTO Veg_Table
FROM (VALUES
   (1,       '12/01/2020'),
   (2,       '12/02/2020'),
   (3,       '12/03/2020'),
   (4,       '12/04/2020'),
   (5,       '12/05/2020'),
   (6,       '12/06/2020'),
   (7,       '12/07/2020'),
   (1,       '12/10/2020'),
   (2,       '12/11/2020'),
   (3,       '12/12/2020')
   )
v (ID,   MyDate)

I have a query that looks something like this...

Select Distinct CTE.ID, *
From (
Select *
From Order_Table as Hist
Inner Join Veg_Table As Veg
On Hist.OrderNum = Veg.ID) as CTE

How can this query be modified to give only unique IDs? I always get duplicate IDs.

I also tried: Where In (Select Distinct ID From Event_View)

That didn't work either.

I want to end up with something like this.

OrderNum    CustomerNum    OrderDesc    Structure   ID    MyDate
1           456            repair       House       1     12/1/2020
2           456            paint        House       2     12/2/2020
3           678            repair       Fence       3     12/3/2020
4           789            repair       House       4     12/4/2020
5           789            paint        House       5     12/5/2020
6           789            repair       Fence       6     12/6/2020
7           789            paint        Fence       7     12/7/2020

I suppose Row_Number() Over (Partition By ID) would do it, but I was hoping for a simpler solution using 'Distinct'.

Assuming Hist.Event_ID is the same as ID that you want, you simply take the * away from the Select Distinct.. that should give you a list of distinct ID from EVENT_VIEW. but if that is all you want, why not just go select distinct EVENT_ID from event_view?? — Harry, Jan 05 '23 at 01:16
If you are looking to get just the "latest" event for each ID, take a look at "[Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group)". — T N, Jan 05 '23 at 01:32

score 1 · Accepted Answer · answered Jan 05 '23 at 04:01

1

Using a regular GROUP BY and MIN appears to give you what you want.

SELECT Hist.OrderNum, Hist.CustomerNum, Hist.OrderDesc, Hist.Structure, MIN(Veg.MyDate)
FROM #Order_Table AS Hist
INNER JOIN #Veg_Table AS Veg ON Hist.OrderNum = Veg.ID
GROUP BY Hist.OrderNum, Hist.CustomerNum, Hist.OrderDesc, Hist.Structure;

answered Jan 05 '23 at 04:01

Dale K

25,246
15
42
71

Oh, now I see it! Thanks! But, why would the distinct NOT work? – ASH Jan 05 '23 at 04:24
1

Distinct *DID* work, distinct applies to all columns, not just the first, so you were getting distinct values of the final column of dates. To be honest, `distinct` has very few actual uses, most times when people are using distinct they don't understand their data or their query properly. – Dale K Jan 05 '23 at 04:27
1

That makes sense. I have rarely used distinct, actually. Thanks again!! – ASH Jan 05 '23 at 04:55

How can we select distinct *, From and get only Distinct IDs?

1 Answers1