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

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

CodePudding user response：

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;