CODEWITHSUNDEEP
sql
Tammy Robinson
Tammy Robinson

Reputation: 15

select all but max record in SQL query

I have been trying to get this to work but I am not getting anywhere. What I need is the following:

I need to be able to select all but the MAX record for a particular table. I know how to select the Max record (by using TOP, or MAX) but I want to display all but that. Is there a way to do this? I have tried the code below, but I keep getting the MAX record instead.

    SELECT 
    rtrim(ltrim(pn.sFirstName + ' ' + pn.uLastName)) as newroom
    FROM tenant t (nolock)
    INNER JOIN room rm (NOLOCK) on t.hmyperson = rm.hmytenant
             and isnull(rm.boccupant,0)=0
             and rm.dtmoveout is null
    INNER JOIN person pn (nolock) on pn.hmy = rm.hmyperson
    WHERE pn.hmy <> 
   (SELECT TOP 1 pn.hmy 
    FROM tenant t (nolock)
    INNER JOIN property p (nolock) on p.hMy = t.hProperty
    INNER JOIN unit u (nolock) on (t.hUnit = u.hMy
    INNER JOIN addr ua (nolock) on u.hmy = ua.hPointer
    INNER JOIN room rm (NOLOCK) on t.hmyperson = rm.hmytenant
             and isnull(rm.boccupant,0)=0
        and rm.dtmoveout is null
        and isnull(rm.dtMoveIn,getdate()) >= getdate()
    INNER JOIN person pn (nolock) on pn.hmy = rm.hmyperson
    WHERE t.code = '011212'
    ORDER BY pn.hmy)
    and t.code = '011212'

After I pull the records, I want to incorporate the MAX record in a separate line.

Thanks for any help.

Upvotes: 0

Views: 2835

Answers (4)

Tammy Robinson
Tammy Robinson

Reputation: 15

Thanks for the input..

I am a lot closer. My next hurdle is trying to separate the remaining records. For instance, there are multiple names linked to the record.

I would like it to be:

NewRoom, NewRoom2

Bob Smith, George Wilson

I get:

NewRoom, NewRoom2

BobSmith

George Wilson

My code below:

   SELECT 
rtrim(ltrim(pn.sFirstName + ' ' + pn.uLastName)) as newroom,
rtrim(ltrim(pn1.sFirstName + ' ' + pn1.uLastName))as newroom2
FROM tenant t (nolock)
INNER JOIN room rm (NOLOCK) on t.hmyperson = rm.hmytenant
         and isnull(rm.boccupant,0)=0
         and rm.dtmoveout is null
INNER JOIN person pn (nolock) on pn.hmy = rm.hmyperson
LEFT OUTER JOIN room rm1 (NOLOCK) on t.hmyperson = rm1.hmytenant
         and isnull(rm1.boccupant,0)=0
         and rm1.dtmoveout is null
LEFT OUTER JOIN person pn1 (nolock) on pn1.hmy = rm1.hmyperson
WHERE (pn.hmy or pn1.hmy) <> 
   (SELECT Max(pn.hmy) 
FROM tenant t (nolock)
INNER JOIN property p (nolock) on p.hMy = t.hProperty
INNER JOIN unit u (nolock) on (t.hUnit = u.hMy
                   and u.sCode not in ('WAIT' ,'COMAREA')
                   and u.scode not like 'NONRES%'
                   and u.exclude = '0' )
INNER JOIN addr ua (nolock) on u.hmy = ua.hPointer
INNER JOIN room rm (NOLOCK) on t.hmyperson = rm.hmytenant
         and isnull(rm.boccupant,0)=0
    and rm.dtmoveout is null
INNER JOIN person pn (nolock) on pn.hmy = rm.hmyperson
WHERE t.scode = 't0029839'

) and t.scode = 't0029839'

Not to mention, I need the MAX record on the same line as well:

MAX_Room, NewRoom, NewRoom2

Thanks everyone

Upvotes: 0

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

In your query: On Line 9 - change it to:

SELECT MAX(pn.hmy)

And remove Line 20. Its not needed.

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

select
    MaxValue = Max(/* whatever*/),
   -- other required columns
from 
:
:
group by -- as needed
having value <> MaxValue

Upvotes: 0

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Basically, you want this:

SELECT * FROM tableA WHERE tableA.ID < (SELECT MAX(ID) FROM tableA)

Upvotes: 0

Related Questions