?
Solved

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

Posted on 2009-05-05
5
Medium Priority
?
801 Views
Last Modified: 2013-11-30
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

0
Comment
Question by:smyers051972
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24306389
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
 
LVL 1

Author Comment

by:smyers051972
ID: 24306490
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
 
LVL 1

Author Comment

by:smyers051972
ID: 24306497
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 24306570


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
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31578091
Perfect. Thanks a bunch!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question