?
Solved

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

Posted on 2009-05-05
5
Medium Priority
?
798 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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