Hello Steve.
> Martin,
> (In case Vyas is still out for coffee)
> In SQL Server, you do not have to include the sort column in a
> simple select query. If that is a restriction because of something else,
do
> this:
> select top 10 strGroup, max(lngSMMID) as maxSMMID
> from tSalgMagasinerMaalgruppe
> group by strGroup
> order by maxSMMID desc
> or
> select strGroup
> from (
> select top 10 strGroup, max(lngSMMID) as maxSMMID
> from tSalgMagasinerMaalgruppe
> group by strGroup
> order by maxSMMID desc
> ) X
> Steve Kass
> Drew University
> > But my problem is, that I need to sort by the index coulmn in order to
get
> > the last 10 strings. And the only way to do this is by including that
column
> > in the SELECT DISTINCT part of the statement. And because the index
column
> > is contains unique values, I'll get duplicate string values in the
> > resultset.
> > > Here's an example:
> > > SELECT DISTINCT TOP 3 ColumnName
> > > FROM TableName
> > > ORDER BY ColumnName DESC
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > http://vyaskn.tripod.com/
message
> > > > Unfortunately I can't use DISTINCT in this situation (if I'm wrong
> > please
> > > > correct me :-). Because I use the SMMID to sort by in order to get
the
> > > last
> > > > values first (I only need the last 10 unique strings). And I must
> > include
> > > > SMMID in the SELECT statement when I use DISTINCT and SMMID is
allways
> > > > unique.
> > > > Do you have any other ideas?
> > > > Martin Moustgaard
meddelelse
> > > > > You might want to modify the SELECT in your INSERT statement to
use
> > > > DISTINCT
> > > > > keyword
> > > > > --
> > > > > HTH,
> > > > > Vyas, MVP (SQL Server)
> > > > > http://vyaskn.tripod.com/
> > > message
> > > > > > I'm using SQL server 7.0, SP3 on NT 4.0 server, SP6a
> > > > > > I have a table, which consists of 2 columns. An Identity (1,1)
and a
> > > > > string
> > > > > > (VARCHAR(255)).
> > > > > > Sometimes I need to return the last 10 distinct strings from
that
> > > table.
> > > > > > I've therefore created the following SP:
> > > > > > CREATE TABLE #TmpGroup (lngSMMID INT, strGroup VARCHAR(255))
> > > > > > INSERT INTO #TmpGroup
> > > > > > SELECT SMMID, SMMGroup FROM tSalgMagasinerMaalgruppe WHERE
SMMGroup
> > > NOT
> > > > IN
> > > > > > (SELECT strGroup FROM #TmpGroup) ORDER BY SMMID DESC
> > > > > > SELECT * FROM #TmpGroup ORDER BY lngSMMID DESC
> > > > > > DROP TABLE #TmpGroup
> > > > > > But this inserts all the records into #TmpGroup. How can this
be? I
> > > > > thought
> > > > > > that the "SMMGroup NOT IN ..." would assure that there would be
no
> > > > > > duplicates.
> > > > > > Martin Moustgaard
by