question

MrEdge-3552 avatar image
0 Votes"
MrEdge-3552 asked EchoLiu-msft commented

Return one column based on date or another criteria

I have a SQL query as below

 with Vehicles as (SELECT distinct
                 t1.Col1,
                 t1.Col2,
                 t1.Col3,
                 t1.Col4,
                 t1.customerID,
             (select top 1 t4.Col1 from table4 As NewColumn where t5.purchaseDate <= CAST(CURRENT_TIMESTAMP as date) order by t5.date desc) As FinalDate
    
 FROM
 table1 t1
    
                 INNER JOIN table2 As t2 ON t1.customerID = t2.customerID AND t1.vehicleId = t2.vehicleId 
                 INNER JOIN table3 As t3 ON t2.customerID = t3.customerID AND t2.nationId = t3.nationId
                 INNER JOIN table4 As t4 ON  t4.vehicleId = t2.vehicleId
                 LEFT JOIN table5 As t5 On t3.customerID = t5.customerID 
                                                 AND t2.vehicleId = t5.vehicleId 
                                                 AND t5.customerID = t4.customerID 
                                                 AND t5.vehicleId = t4.vehicleId 
                                                 AND t5.areamgrId = t4.areamgrId)

Select * from Vehicles where.....

This runs as intended until i add in t4.Col1 (the inner select statement). Adding this in means i get extra rows back as the values in the column are different.

I tried to add some logic to say 'get me the first value where its earlier than todays date' i add a desc so it sorts by the most recent date and then selects that. Even doing this returns extra rows. I would like to do this again for another column with a different where clause too.

What am i doing wrong?

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For this type of problem we recommend that you post CREATE TABLE statements
for your tables together with INSERT statements with sample data,
We also need to see the expected result of the sample.

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

What am i doing wrong?

Your mistake is that you have not analysed your requirements well enough. You have SELECT DISTINCT on five columns. The you add a sixth column, and you get more combinations, because apparently for every quintuple there can be more than one value of t4.col1. You need to make a decision on which value you want, and compose your query from that rule.

That may be a little abstract, but I don't know your tables, so I cannot discuss in more specific terms.

By the way, as the query is given above, I don't really see what table5 ia doing there. You are not reading any rows from it, and it is a LEFT JOIN, so cannot serve as a filter. Table3 also hangs in the air a bit, but since that is an inner join, maybe that is a filter.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.