Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with a query for a DB that has over 1 mil records. The DB
contains over 30 regions and I want the query for 50 records only for each
region. Can anyone help?
 
Hi Eagles73,

The first thing that you will have to do is order the query by the
field taht you want to get the top 50 off. Then you must enter 50 the
'top values' box or if you are editing your sql directly type SELECT
TOP 50 at the beginning of your sql statement.

Good luck

Nick
 
Nick said:
Hi Eagles73,

The first thing that you will have to do is order the query by the
field taht you want to get the top 50 off. Then you must enter 50 the
'top values' box or if you are editing your sql directly type SELECT
TOP 50 at the beginning of your sql statement.
Click to expand...

That will give him the top 50 records from the query, but not the top 50
*per region*.
 
Generically, you would need something like

SELECT Region, OtherField
FROM [Your Table]
WHERE YourTable.PrimaryKeyField in
(SELECT TOP 50 PrimaryKeyField
FROM [Your Table] as Temp
WHERE Temp.Region = [Your Table].Region
ORDER BY Temp.OtherField, Temp.PrimaryKeyField)

For more specifics, you might try posting the Top 50 query you used that got
the Top 50 from the entire table. Then someone might be able to modify it
to get what you want.
 
There's probably an easier way, but you could "muscle" it by creating a
separate query for each region, using the region name as the criteria, they
using a union query to stack those query results together.
 
OK, look closely at my answer the first thing that I tell you to do is
order the records by the field you want the top fields of. I was
presuming that you fed in which region you wanted to see. however if
you want to see all the regions at once you must create a aggregate or
totals query grouping by region.

Good luck
 
Nick 'The database Guy' said:
OK, look closely at my answer the first thing that I tell you to do is
order the records by the field you want the top fields of. I was
presuming that you fed in which region you wanted to see. however if
you want to see all the regions at once you must create a aggregate or
totals query grouping by region.
Click to expand...

Nick, that won't give him the top 50 per region either. That will give him all
records per region.
 
Thanks for your help. This works great for a small database but for mine
that was over 1 mil records, it would not. I had to manually delete records
to a level under 100 K before it worked, but I do appreciate it.

John Spencer said:
Generically, you would need something like

SELECT Region, OtherField
FROM [Your Table]
WHERE YourTable.PrimaryKeyField in
(SELECT TOP 50 PrimaryKeyField
FROM [Your Table] as Temp
WHERE Temp.Region = [Your Table].Region
ORDER BY Temp.OtherField, Temp.PrimaryKeyField)

For more specifics, you might try posting the Top 50 query you used that got
the Top 50 from the entire table. Then someone might be able to modify it
to get what you want.

eagles73 said:
Sorry, but what I meant to say was that it did not give me the 50 per
region.
Click to expand...
Click to expand...
 
If you apply other criteria to the outer query. Such as "Region Between x and
y", then the query engine might use that criteria to limit the records it is
checking.

An alternative would be to use a divide and conquer approach and do a region at
a time and then pump that into a temporary work table using an append query.

Thanks for your help. This works great for a small database but for mine
that was over 1 mil records, it would not. I had to manually delete records
to a level under 100 K before it worked, but I do appreciate it.

John Spencer said:
Generically, you would need something like

SELECT Region, OtherField
FROM [Your Table]
WHERE YourTable.PrimaryKeyField in
(SELECT TOP 50 PrimaryKeyField
FROM [Your Table] as Temp
WHERE Temp.Region = [Your Table].Region
ORDER BY Temp.OtherField, Temp.PrimaryKeyField)

For more specifics, you might try posting the Top 50 query you used that got
the Top 50 from the entire table. Then someone might be able to modify it
to get what you want.

eagles73 said:
Sorry, but what I meant to say was that it did not give me the 50 per
region.

:

Hi Eagles73,

The first thing that you will have to do is order the query by the
field taht you want to get the top 50 off. Then you must enter 50 the
'top values' box or if you are editing your sql directly type SELECT
TOP 50 at the beginning of your sql statement.

Good luck

Nick

eagles73 wrote:
I need help with a query for a DB that has over 1 mil records. The DB
contains over 30 regions and I want the query for 50 records only for
each
region. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top