Query to Select the Top Value

[jenmerr]

Hi

I need some help in writing a query to select the top bit for each car in a query.
The code that i have so far selects the top value but wont return each car.

<cfquery name="qBidSelect1" datasource="#request.dsName#">
select top 1 Bid.Bid,
Bid.CustomerID ,
Bid.TenderID ,
Tender.TenderName ,
Customer.CustomerName,
Car.CarID,
Car.CarNumber ,
Car.MakeAndModel ,
Car.RegNumber ,
Car.Mileage ,
Car.Garage ,
Car.Salesman

from Bid
join Customer on Customer.CustomerID = Bid.CustomerID
Join Car on Car.CarID = Bid.CarID
join Tender on Tender.TenderID = Bid.TenderID

where

1 = 1
Order by Bid DESC

</cfquery>

Would really appreciate someones help

Thanks
J

 

[cfSearching]

The TOP operator onlys return the first X records in your SELECT list.

You need to determine how the "top bid" by car is determined. Is the latest bid by some date column, the one with the highest amount, etcetera? What columns are involved?

----------------------------------

http://cfsearching.blogspot.com/

 

[FALCONSEYE]

with top 1 bid and order by bid desc statement, you should be able to get the top record. What you have and what you are asking is a little bit conflicting. If you need to return all of the cars, then you should not do top 1. If you want to return top 1 within a "group" of cars, then you will need to add a "group by" statement.

http://www.fuzzysiberians.com

 

[cfSearching]

If you want to return top 1 within a "group" of cars, then you will need to add a "group by" statement.

That is true. I suspect what they are trying to do is return a list of cars, and the top bid for each one. So they may also need to use a derived query. But first thing is clarify

a) what data should be returned in the query
b) how are you defining "top bid" by car

----------------------------------

http://cfsearching.blogspot.com/

 

[r937]

SELECT Car.CarID
     , Car.CarNumber 
     , Car.MakeAndModel 
     , Car.RegNumber 
     , Car.Mileage 
     , Car.Garage 
     , Car.Salesman     
     , Bid.Bid
     , Bid.CustomerID 
     , Bid.TenderID 
     , Tender.TenderName 
     , Customer.CustomerName
  FROM Car
[blue]INNER
  JOIN ( SELECT CarID
              , MAX(Bid) AS max_bid
           FROM Bid
         GROUP
             BY CarID ) AS m[/blue]
INNER
  JOIN Bid 
    ON Bid.CarID = Car.CarID 
   [blue]AND Bid.Bid = m.max_bid[/blue]
INNER
  JOIN Tender 
    ON Tender.TenderID = Bid.TenderID  
INNER
  JOIN Customer 
    ON Customer.CustomerID = Bid.CustomerID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cfSearching]

Rudy is definitely on the right track. But we still need to know how the OP is defining "top bid". By recordID, date, amount, etcetera.

So jenmerr, help us out here

----------------------------------

http://cfsearching.blogspot.com/

 

[r937]

the OP defined "top bid" right there in post #1 -- it's the highest Bid.Bid amount, based on TOP together with ORDER BY DESC



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cfSearching]

Well there goes the "Teach a man to fish ..." approach



----------------------------------

http://cfsearching.blogspot.com/

 

[jenmerr]

Hi
Thanks for your help.
I need to find the highest Bid for each Car. A number of bids would be placed on a particular car so then i need to find out the winning bid that was placed on each car. Hope that makes sense.. I have another query looking at the Car end of things searching for the customer with the higest Bid. Its just another way of looking at it. But i cant export that query to a CSV file. I use a query within another query to find the values. Rather than one big query.


<cfquery name="qCarSelect" datasource="#request.dsName#">
select Car.CarID ,
Car.TenderID ,
Tender.TenderName ,
Car.CarNumber ,
Car.MakeAndModel ,
Car.RegNumber ,
Car.Mileage ,
Car.Garage ,
Car.Salesman ,
Car.DateCreated ,
Car.DateLastUpdated ,
Car.CreatedBy ,
Car.UpdatedBy
from Car

join Tender on Tender.TenderID = Car.TenderID

where car.tenderid = '#url.tenderid#'

<cfif form.garage gt "">
and Car.garage = '#form.garage#'
</cfif>
order by CarNumber ASC
</cfquery>

...
...


<cfoutput query="qCarSelect" >
...
....

<cfquery name="qBidSelect" datasource="#request.dsName#">
select top 1 Bid.Bid,
Bid.CustomerID ,
Customer.CustomerName

from Bid
join Customer on Customer.CustomerID = Bid.CustomerID
where bid.carid = '#qCarSelect.carid#'
Order by Bid DESC

</cfquery>


</cfoutput>

So that's where i got the use of the TOP element.
If i could use either query it would be great. at the end of the day i just want to have one query to export.

Thanks for all your help so far.

 

[jenmerr]

Sorry just to update, I use the word top but really mean the Highest Bid.
And when i said either query, i mean the first one i posted yesterday or the second one that i posted today..

 

[r937]

did you try my query?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[FALCONSEYE]

you will need to use max(Bid.Bid) instead and group by.

http://www.fuzzysiberians.com

 

[Bammy217]

just use a group by clause, ensure you have only the highest bid

<cfquery name="qBidSelect1" datasource="#request.dsName#">
SELECT MAX(Bid.Bid) AS Bid, Bid.CustomerID, Bid.TenderID,  Tender.TenderName , Customer.CustomerName, Car.CarID, Car.CarNumber , Car.MakeAndModel , Car.RegNumber , Car.Mileage , Car.Garage , Car.Salesman

FROM Bid join Customer on Customer.CustomerID = Bid.CustomerID Join Car on Car.CarID = Bid.CarID
join Tender on Tender.TenderID = Bid.TenderID
GROUP BY Bid.Bid, Bid.CustomerID, Bid.TenderID,  Tender.TenderName , Customer.CustomerName, Car.CarID, Car.CarNumber , Car.MakeAndModel , Car.RegNumber , Car.Mileage , Car.Garage , Car.Salesman          
</cfquery>

it might need some tweaking, but that should give you approximately what you are after

Cheers,
B.

We never fail, we just find that the path to succes is never quite what we thought...

 

[r937]

that for sure will require tweaking, bammy

for example, you have

GROUP BY Bid.Bid...

this will ensure that each bid is its own max in the results

has anybody tried, or even looked at, the query in post #5 ??



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon