Show First and Last Record from Ascended List

[ttp]

Below is a snapshot of some data I am evaluating:
ShopOrder ICN
F4702G1 CR199889
F4702G3 CR355486
F4703G3 CR330389
F4703G3 CR330391
F4703G3 CR352765
F4703G3 DR353975-1; where ShopOrder and ICN are fields

I want to group the shop order under one record and show the first ICN and
last ICN. The ICN is sorted ascending.

I am using the design grid to configure. But, below is a copy of the SQL
that was created from the application:
SELECT [Allocation Table 06-20-2008].ShopOrder, Count([Allocation Table
06-20-2008].ShopOrder) AS CountOfShopOrder, First([GC export].ICN) AS
FirstOfICN, Last([GC export].ICN) AS LastOfICN1
FROM [Allocation Table 06-20-2008] LEFT JOIN [GC export] ON [Allocation
Table 06-20-2008].ShopOrder = [GC export].[Shop Order]
WHERE ((([GC export].ICN) Like "CR*")) OR ((([GC export].ICN) Like "DR*"))
GROUP BY [Allocation Table 06-20-2008].ShopOrder
HAVING (((Count([Allocation Table 06-20-2008].ShopOrder))>1))
ORDER BY [Allocation Table 06-20-2008].ShopOrder, First([GC export].ICN),
Last([GC export].ICN);

I want the SQL to show the first and last record from the ascended list.
But, the query is not picking from the ascended list; but it is arbitrarily
showing the ICN not in the ascended order.

How can I get the first and last ICN to show from the ascended list?

 

[Bob Barrows [MVP]]

Below is a snapshot of some data I am evaluating:
ShopOrder ICN
F4702G1 CR199889
F4702G3 CR355486
F4703G3 CR330389
F4703G3 CR330391
F4703G3 CR352765
F4703G3 DR353975-1; where ShopOrder and ICN are fields

I want to group the shop order under one record and show the first
ICN and last ICN. The ICN is sorted ascending.

Select ShopOrder, Min(ICN) As FirstICN, Max(ICN) As LastICN
From table
Group By ShopOrder

 

[Lou]

Select ShopOrder, Min(ICN) As FirstICN, Max(ICN) As LastICN
From table
Group By ShopOrder

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Try something like this:

SELECT A.ShopOrder, A.ICN as MinICN, B.ICN as MaxICN
from Table1 as A inner join Table1 as B
on A.ShopOrder = B.ShopOrder
where A.ICN = ( SELECT MIN( ICN ) from Table1 where ShopOrder =
A.ShopOrder )
and B.ICN = ( SELECT MAX( ICN ) from Table1 where ShopOrder =
B.ShopOrder )
group by A.ShopOrder, A.ICN, B.ICN
order by A.ShopOrder, A.ICN, B.ICN

I'm not sure if the ascending order was to be on ShopOrder, MinICN or
MaxICN. Therefore, the "order by" line may need to be respecified.

 

[ttp]

Thanks, Bob Barrows. This gave me what I was looking for.