SQL Syntax Help

[allyne]

Hi Everyone,
I'm using SQL 7.0 and
I have a table with the following fields:

BID, Current, 30, 60

This table has hundreds of bids. What I need to do is write a SQL statement that will pull the top 35 records for each bid (without having to specify what each bid is). something like (This is wrong)

SELECT TOP 35 BID, Current, 30, 60
FROM tblMarket
GROUP BY BID

Is there a way to do this?

Thanks for all your help
Cathy

 

[DLanouette]

YOu are on the right track. If you are going to "group by bid", you need to do something with Current. But, you probably want to "order by bid desc"


HTH


______________________________
- David Lanouette
- Lanouette Consulting, LLC
- DLanouette@Computer.org

 

[allyne]

Getting Closer.... I tried
SELECT BID, Current, 30, 60
FROM tblMarket
GROUP BY BID, Current, 30, 60
ORDER BY BID ASC, Current DESC

This gives me what I want except with all the records in each group. Now I want to be able to Select the Top 35 Records in each group.

I tried
SELECT Top 35 BID, Current, 30, 60
FROM tblMarket
GROUP BY BID, Current, 30, 60
ORDER BY BID ASC, Current DESC

but this gives me the top 35 records and not the top 35 records in each group. Is there a way to do this?

Thanks for your help.
Cathy

 

[tlbroadbent]

Try this query.

SELECT Bid, Current
FROM tblMarket AS a
WHERE
(SELECT count(*) FROM tblMarket
WHERE Bid=a.Bid
AND Current>a.Current) < 35
ORDER BY a.Bid, a.Current DESC

Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[allyne]

Thanks for all your help! It worked! I apologize for not responding sooner but I've been out of town......