Return rows with less than max 2

[hapax]

The following query...

SELECT ItemId, NoteSeqNum
FROM NotesTable
WHERE ItemId = 6434
AND NoteTypeId = 1

..returns these results:

ItemId NoteSeqNum
6434 11
6434 12
6434 13


I need SQL to return only those items with less than the max NoteSeqNum. So this would be my desired result:

ItemId NoteSeqNum
6434 11
6434 12

 

[markros]

SELECT N.ItemId, N.NoteSeqNum
FROM NotesTable INNER JOIN (SELECT ItemId, Max(NoteSeqNum) as MaxSeq
FROM NotesTable N
WHERE ItemId = 6434
AND NoteTypeId = 1 group by ItemID) maxItems ON N.ItemID = maxItems.ItemID
WHERE ItemId = 6434 and N.NoteSeqNum < maxItems.MaxSeq
AND N.NoteTypeId = 1

 

[bborissov]

Grrrr
Vultures, ha
You aliased the table in derived table not in the main query so you can't use SELECT N. ....

SELECT [COLOR=red][b]N.[/b][/color]ItemId,
       [COLOR=red][b]N.[/b][/color]NoteSeqNum
         FROM NotesTable 
INNER JOIN (SELECT ItemId, Max(NoteSeqNum) as MaxSeq
                   [COLOR=red]FROM NotesTable N[/color]
            WHERE ItemId     = 6434 AND 
                  NoteTypeId = 1
            GROUP BY ItemID) maxItems
      ON [COLOR=red][b]N.[/b][/color]ItemID = maxItems.ItemID
      WHERE /*here also you should have an alias :-)*/ItemId = 6434 AND
            [COLOR=red][b]N.[/b][/color]NoteSeqNum < maxItems.MaxSeq AND
             [COLOR=red][b]N.[/b][/color]NoteTypeId = 1

So the final result should be:

SELECT N.ItemId, N.NoteSeqNum
       FROM NotesTable N
INNER JOIN (SELECT ItemId, Max(NoteSeqNum) as MaxSeq
                   FROM NotesTable
            WHERE ItemId = 6434 AND NoteTypeId = 1
            GROUP BY ItemID) maxItems 
       ON N.ItemID = maxItems.ItemID
WHERE N.ItemId = 6434                AND
      N.NoteSeqNum < maxItems.MaxSeq AND
      N.NoteTypeId = 1

It is easier to criticize somebody than to invent something by yourself (that is for me )


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Good point, Boris - I mixed up the aliases - two many forums / programs at once.

Time to get mover done and start on the finalizing the form's logic