create a sub-query 1

[Cabrita]

Hello,

I want to make a query that is grouped by code-bar field and has to data fields: data1 and data2

Data2 - the grouping field - so it has all values

Data1 - in each line has the maximum value but under the value of data2 of the line

Table1
cod-bar data1
123 01-01-2004
123 10-01-2004

Table2
cod-bar data2
123 05-01-2004
123 12-01-2004


Query
cod-bar data1 data2
123 01-01-2004 05-01-2004
123 10-01-2004 12-01-2004

Thank you very much

Best regards

 

[Cabrita]

I started with this query, can anyone give an hand

SELECT *
FROM Table1
WHERE DATA1=(select max([data1]) from table1 where [data1]>[data2])))
GROUP BY ....;

thank you very much

 

[rac2]

This kind of query is not really going to work.

SELECT *
FROM Table1
WHERE DATA1=(select max([data1]) from table1 where [data1]>[data2])))
GROUP BY ....;

Because every column in the SELECT clause must be used in the GROUP BY clause. Generally SELECT * would not be what you want.

Lets look at the subquery.

select max(data1) from table1 where data1 > data2

We know that MAX(data1) is greater than any value of data1. Including those values which are greater than data2. So why is that subquery different than this one?

select max(data1) from table1

So you probably meant

select max(data1) from table1 where data1 < data2

which would be the largest value of data1 less than data2.

SELECT Table1.cod-bar, Priors.PriorDate, Priors.data2
FROM Table1
JOIN (
   SELECT cod-bar, data2, MAX(data1) AS PriorDate
   FROM table1
   WHERE data1 < data2
   GROUP BY cod-bar, data2
   ) Priors ON Priors.cod-bar = Table1.cod-bar

This ought to yield one row for each cod-bar and data2.

Try defining a query with the subquery first and see if it yields the rows you need.