How can I select only the rows I need?

[bobquan]

I have spent so much time on this but I cannot get it return only those rows where "Sorter" is unique and rate is the lowest for that group (Sorter)

The table looks like this:

Sorter Lender Loan Rate Price APR
101 1 $49,999.00 5.250% -0.450% 6.256%
101 2 $49,999.00 5.500% -0.875% 6.344%
101 3 $49,999.00 5.750% -0.750% 6.486%
101 4 $49,999.00 5.875% -0.893% 6.536%
102 2 $100,000.00 5.250% -0.560% 6.070%
102 1 $100,000.00 5.500% -0.875% 6.169%
102 3 $100,000.00 5.750% -0.750% 6.308%
103 3 $125,000.00 5.250% -0.560% 6.036%
103 2 $125,000.00 5.500% -0.875% 6.134%

What I want to select and what I am looking for would look like this:

Sorter Lender Loan Rate Price APR
101 1 $49,999.00 5.250% -0.450% 6.256%
102 2 $100,000.00 5.250% -0.560% 6.070%
103 3 $125,000.00 5.250% -0.560% 6.036%

Any help will be greatly appreciated.

Bob

 

[wazz]

try playing with the MIN function (applied to rate). you might also need SELECT DISTINCT and/or the TOP function.

 

[Jon K]

You can get the min Rate of each Sorter in a Totals Query and join the query back to the table in another query to retrieve the records.

qryOne:-
SELECT Sorter, Min(Rate) AS MinOfRate
FROM [TableName]
GROUP BY Sorter;

qryTwo:-
SELECT [TableName].*
FROM [TableName] INNER JOIN qryOne ON ([TableName].Sorter = qryOne.Sorter) AND ([TableName].Rate = qryOne.MinOfRate);


Run the second query.
.

 

[bobquan]

Thank you Jon K

Once I saw how you did it it made complete sense.

I am very appreciative for you help.

Bob

 

[Cosmos75]

You can also do it in one query by using a SQL statement as criteria for you [Rate] field.

SELECT Sorter, Lender, Loan, Rate, Price, APR
FROM tblData
WHERE ((Rate)=(SELECT Min(a.Rate) AS MinOfRate FROM tblData AS a GROUP BY a.Sorter HAVING (a.Sorter= tbldata.Sorter)));

 

[Jane221]

I'm learning SQL statements and would like to ask you experts a question.

Is it advisable to use as few queries as possible?

(I ask because I have no difficulties in understanding the two SQL statements in Jon's post, but I have not come across the SQL syntax in Cosmos75 post before.)

Many thanks.

 

[EMP]

Jane,

To combine two queries into one invariably involves the use of a correlated subquery (that is a SQL statement in a pair of brackets that refers back to the field(s) in the main query.) In Access, an inner join is considered more efficient than a correlated subquery.

So my answer to your question is: It depends.

If there are not too many records, you won't see their difference in performance. But if there are many records to process and the system is not a fast one, avoid correlated subqueries. In the latter case, two queries is better than one that has to use a correlated subquery.

EMP

^

 

[Jane221]

Thanks, EMP. I ran a test and found that two queries with an inner join did run faster than a query with a correlated subquery.

I'm glad I found these forums. I can learn a lot here.