top 10 issue

[longhair]

afternoon all,
having a slight issue with a top 10.
have the following code:

SELECT [CustCode], [SKU], [ConfirmedDate], [pick-ticket-num]
FROM [Table1] 
WHERE  [ConfirmedDate] IN
(SELECT TOP 10 a.[ConfirmedDate]
FROM [Table1]  a
WHERE
(Table1.[pick-ticket-num] = a.[pick-ticket-num]) AND (Table1.SKU = a.SKU) AND (Table1.CustCode = a.CustCode)
ORDER BY [a].[CustCode] DESC);

however this returns more than the top 10 confirmdate.
i also tried:

SELECT [Table1].[CustCode], [Table1].[SKU], a.[ConfirmedDate]
FROM [Table1] INNER JOIN [SELECT TOP 10 [ConfirmedDate] as [First10Ship],  [SKU], [pick-ticket-num]
FROM [Table1]
GROUP BY [SKU]]. AS a ON (Table1.[pick-ticket-num] = a.[pick-ticket-num]) AND (Table1.SKU = a.SKU) AND (Table1.CustCode = a.CustCode)
ORDER BY [Table1].[CustCode];

but this returns an error in the 'from' clause.
i am attempting to get the first 20 confirmed dates for each sku and each custcode.
any suggestions would be appreciated.
regards,
longhair

 

[dhookom]

Did you check other threads first? Does thread701-1566167 work for you?

Duane
Hook'D on Access
MS Access MVP

 

[longhair]

dhookom,
yes i did search the forum first. and the link to the thread where you posted the sql code is where i got the idea for my first example.
had a typo in what i am trying to accomplish, it is the first 10 confirmeddate not the first 20.
however, as i said, the first example returns more than 10 records for each sku and each custcode.
regards,
longhair

 

[alvechurchdata]

You might find it easier if you split the problem into two queries. Write a query to select all the records matching your criteria and put them into date order. Then write a second query to pull the top ten out of that first query.

The approach you're using going to hit problems if you get more than one entry for the same date. Your subquery is picking the top ten dates but what happens when there are more than ten orders on those ten dates.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[dhookom]

If you are wanted by the top ten ConfirmedDate you shouldn't be ordering the subquery by CustCode.

SELECT [CustCode], [SKU], [ConfirmedDate], [pick-ticket-num]
FROM [Table1] 
WHERE  [ConfirmedDate] IN
(SELECT TOP 10 a.[ConfirmedDate]
FROM [Table1]  a
WHERE
(Table1.[pick-ticket-num] = a.[pick-ticket-num]) AND (Table1.SKU = a.SKU) AND (Table1.CustCode = a.CustCode)
ORDER BY [a].[ConfirmedDate] DESC);

Duane
Hook'D on Access
MS Access MVP