Board index » delphi » Delphi 5 + SQL + INNER JOIN

Delphi 5 + SQL + INNER JOIN

I am using an inner join statement like the following:

Table1 A INNER JOIN Table 2 B
On(A.No : = B.No)
Where (Date : = Date)

Example :

(Sample Data)       No field is in Table 2

No = 1 FieldX = 60         No = 2 FieldX = 10
No = 1 FieldX = 70         No = 2 FieldX = 15
No = 1 FieldX = 80         No = 2 FieldX = 20

I need a way to select only the data with the lowest value
for Field X for each No. In this case the following will be
the data returned:

No = 1 Field X = 60
No = 2 Field X = 10

Thanks alot!!!
Crystal

 

Re:Delphi 5 + SQL + INNER JOIN


Quote
"Crystal Bragg" <crys...@mandatasys.com> wrote in message

news:3e662185@newsgroups.borland.com...

Quote

> Table1 A INNER JOIN Table 2 B
> On(A.No : = B.No)
> Where (Date : = Date)

> Example :

> (Sample Data)       No field is in Table 2

> No = 1 FieldX = 60         No = 2 FieldX = 10
> No = 1 FieldX = 70         No = 2 FieldX = 15
> No = 1 FieldX = 80         No = 2 FieldX = 20

> I need a way to select only the data with the lowest value
> for Field X for each No. In this case the following will be
> the data returned:

> No = 1 Field X = 60
> No = 2 Field X = 10

Are all FieldX in table A or are you trying to get the lowest value from two
different tables?

Assuming FieldX is just in one table then just use MIN and Group By:

select A.No, A.MIN(FieldX) from Table1 A
inner join Table2 B on A.No = B.No
Where (Date = :Date)
Group By A.No

I assume you need fields from Table 2 as well, they also need to appear in
the Group By:

select A.No, B.Thing, A.MIN(FieldX) from Table1 A
inner join Table2 B on A.No = B.No
Where (Date = :Date)
Group By A.No, B.Thing

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Other Threads