return X number of rows from a query regardless of Where match

[vbrunner]

Hi

I would like to return X number of rows from a query and sequentially number one of the fields regardless of matching the Where clause.

ie: I would like this to return 10 records (with the field Nbr numbered sequentially) even if no data matches &quot;Price <= 10&quot;:
Select Nbr, Type, Price
From Prod
Where Price <= 10
Group By Nbr
Order BY Nbr

Thanks in advance
I know I've done this before in stored procs but I can't remember right now where to find them (I'm not doing this in a stored proc anyway).

 

[KornGeek]

I don't understand. If no records match, then how will it know what to return?

 

[dodge20]

by &quot;even if no data matches &quot;Price <= 10&quot;&quot; Do you mean that it is null?

If so

Select Nbr, Type, Price
From Prod
Where Price <= 10
or Price is null
Group By Nbr
Order BY Nbr


Dodge20

 

[vbrunner]

Thanks,

I decided to go with a temp table (populate with X# of records then update from select)

Phil

 

[danvlas]

Why would you want to number the records in a query?

You can make a Select Top X query to return the number of records you want, then create a report and number the lines in it-really simple: text box in detail section, control source: =1, RunningSum: Over All.

A temporary table is a poor solution IMHO, as the table might be overwritten by someone else right before you want to use it...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html