mssql t-sql select only min or max value from temp table

I have a script shown below, that selects only people with identical last name and address (street only) as well as their casino net worth (theo win).  I want to select out of this temp table ONLY the lowest theo win so I can delete them from my report temp table and am stuck.. Keep in mind, its finding the duplicate addresses and last name, i just want to do something like:

delete from #report where (this is where it should be based on select from another temp table from my script below as I would change the script below to a select into #other_table) for an example...

Thanks for your help!

select r.player_id,r.firstname,r.lastname,r.address1a, r.totaltwin
from   #report r
    inner join
       (
        select lastname,address1a
        from   #report
        group by lastname,address1a
        having count(*) > 1
        ) dr
    on  r.lastname = dr.lastname
    and r.address1a = dr.address1a

Open in new window

LVL 1
smyers051972Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:


select r.player_id,r.firstname,r.lastname,r.address1a, r.totaltwin, dr.minTotalTWin
from   #report r
    inner join
       (
        select lastname,address1a, MIN(totalTWin) minTotalTWin
        from   #report
        group by lastname,address1a
        having count(*) > 1
        ) dr
    on  r.lastname = dr.lastname
    and r.address1a = dr.address1a
   -- and r.totaltwin =  dr.minTotalTWin

if you want to delete the entries with minimum TotalTwin, then uncomment the last line
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select r.player_id,r.firstname,r.lastname,r.address1a, r.totaltwin
from   #report r
    inner join
       (
        select lastname,address1a
        from   #report
        group by lastname,address1a
        having count(*) > 1
        ) dr
    on  r.lastname = dr.lastname
    and r.address1a = dr.address1a
WHERE r.totaltWin  =  (SELECT MIN(totalTWin) from #Report )
0
 
smyers051972Author Commented:
Whats funny is I tried it that way but in reverse, selected the MAX values, but got only 1 result.  When I tried it that way it selected 0 records.  What I am trying to do is eval the last name, and address, if they are matching records then I would want to turn around and delete those records from the #report table.

This would be a mailing list...
0
 
smyers051972Author Commented:
I think the way you mentioned above was only selecting the absolute record not each of the two records that is lowest but its strange I have 0 results from that.
0
 
smyers051972Author Commented:
Perfect. Thanks a bunch!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.