Top property for multiple columns?

[dan.gates]

Hello -
Is it possible to use some sort of Top property or another command to
limit the number of records per column?

For example:
In this query...

INSERT INTO [Top 50] ( [Size], 15 )
SELECT TOP 50 v_item_schematics.size, Cereal.[15]
FROM Cereal LEFT JOIN v_item_schematics ON Cereal.AG =
v_item_schematics.ag_item_cd
ORDER BY v_item_schematics.avg_mvmt DESC;

I am selecting the Top 50 items from the Cereal table.

Can I add another table, say "Soup", and show the Top 50 in that as
well. Thus resulting in 100 records retrieved?

Thanks for any assistance someone can provide!
Dan

 

[MGFoster]

Hello -
Is it possible to use some sort of Top property or another command to
limit the number of records per column?

For example:
In this query...

INSERT INTO [Top 50] ( [Size], 15 )
SELECT TOP 50 v_item_schematics.size, Cereal.[15]
FROM Cereal LEFT JOIN v_item_schematics ON Cereal.AG =
v_item_schematics.ag_item_cd
ORDER BY v_item_schematics.avg_mvmt DESC;

I am selecting the Top 50 items from the Cereal table.

Can I add another table, say "Soup", and show the Top 50 in that as
well. Thus resulting in 100 records retrieved?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd use a UNION QUERY (untested):

INSERT INTO [Top 50] ( [Size], [15] )
SELECT size, [15]
FROM (
(SELECT TOP 50 v_item_schematics.size, Cereal.[15]
FROM Cereal LEFT JOIN v_item_schematics ON Cereal.AG =
v_item_schematics.ag_item_cd
ORDER BY v_item_schematics.avg_mvmt DESC) As C

UNION

(SELECT TOP 50 v_item_schematics.size, Soup.[15]
FROM Soup LEFT JOIN v_item_schematics ON Soup.AG =
v_item_schematics.ag_item_cd
ORDER BY v_item_schematics.avg_mvmt DESC) As S
) AS A

If there is a tie you may get more than 50 per SELECT.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQuB42IechKqOuFEgEQKpaACfTTRRxBx9Jq9NAdE9mNJm7k9d004AoNh0
CFMwVN6Ys3ZKN6BkfQ4bTFxY
=oyj9
-----END PGP SIGNATURE-----

 

[dan.gates]

Thanks....I'll give this a try!

 

[peregenem]

Is it possible to use some sort of Top property or another command to
limit the number of records per column?

Can I add another table, say "Soup", and show the Top 50 in that as
well. Thus resulting in 100 records retrieved?

Access/Jet does have a TOP N syntax but it has a number of problems
associated with it: proprietary and nonportable, uses a cursor (hence
the requirement for ORRDER BY) making it non-relational, problems with
the cursor engine give erroneous results, etc.

The standard way of performing such operations is to use a subquery.
BTW 'size' is a reserved word pretty much every where (Jet, odbc,
SQL-92, SQL-99, etc) and must be escaped. Also, you are using two
columns to INSERT to a table which seemingly has no key (strictly not a
table, then, but a heap) so I've added an additional column 'source'
for clarity

SELECT V1.[size], C1.[15],
'Cereal' AS source
FROM Cereal AS C1
LEFT JOIN v_item_schematics AS V1
ON C1.AG = V1.ag_item_cd
WHERE 50 >= (
SELECT COUNT(*) FROM Cereal
LEFT JOIN v_item_schematics
ON Cereal.AG = v_item_schematics.ag_item_cd
WHERE v_item_schematics.avg_mvmt > V1.avg_mvmt)
UNION ALL
SELECT V1.[size], S1.[15],
'Soup' AS source
FROM Cereal AS S1
LEFT JOIN v_item_schematics AS V1
ON S1.AG = V1.ag_item_cd
WHERE 50 >= (
SELECT COUNT(*)
FROM Soup
LEFT JOIN v_item_schematics
ON Soup.AG = v_item_schematics.ag_item_cd
WHERE v_item_schematics.avg_mvmt > V1.avg_mvmt);

 

[peregenem]

SELECT TOP 50

If there is a tie you may get more than 50 per SELECT.

See my post for an alternative approach which handles ties correctly -
another reason not to use TOP N, then!

 

[dan.gates]

Thanks for the help, but if I could please get a little more fine
tuning experience that would be great! I appreciate all the learning
this group is teaching me.

My ulitmate goal is to get the first 50 results back from the [15]
columns where the field is 0 (zero).

Here is the SQL statement as it now...

SELECT V1.[item_desc],
V1.[size],V1.[uom_desc],V1.[p_upc_alpha],V1.[ag_item_cd],v1.[status_cd],v1.[avg_mvmt],C1.[15],
'CEREAL' AS Sub_Category
FROM Cereal AS C1
LEFT JOIN v_item_schematics AS V1 ON C1.AG = V1.ag_item_cd

WHERE 50 >= (SELECT COUNT(AG) FROM Cereal
LEFT JOIN v_item_schematics ON Cereal.AG = v_item_schematics.ag_item_cd

WHERE v_item_schematics.avg.mvmt > v1.avg_mvmt)

UNION ALL

SELECT V1.[item_desc],
V1.[size],V1.[uom_desc],V1.[p_upc_alpha],V1.[ag_item_cd],v1.[status_cd],v1.[avg_mvmt],S1.[15],
'SOUP' AS Sub_Category
FROM Soup AS S1
LEFT JOIN v_item_schematics AS V1 ON S1.AG = V1.ag_item_cd

WHERE 50 >= (SELECT COUNT(AG) FROM Soup
LEFT JOIN v_item_schematics ON Soup.AG = v_item_schematics.ag_item_cd
WHERE v_item_schematics.avg.mvmt > v1.avg_mvmt)

order BY Sub_category,v1.[avg_mvmt] DESC;

Right now the results are 925 records, 243 being from the first table
and 682 from the second.

Thanks again everyone!