getting distinct rows in sql

I am trying  to get the max(ofuploadtime) per toolnumber. In the attachment you will see that there are multiple uploadtimes for toolnumbers i only want one foruploadtime for each tool number

SELECT  DISTINCT toolnumber, 
       lastoflocation as lastoflocation, 
       MAX(maxofuploadtime) as maxofuploadtime, 
       jobtype, 
       company, 
       rig, 
       rignumber, 
       wellname, 
       city, 
       state 
FROM   temp_lkltn
GROUP BY toolnumber,lastoflocation, jobtype, 
       company, 
       rig, 
       rignumber, 
       wellname, 
       city, 
       state 
[embed=file 903555]

Open in new window

Capture.PNG
LVL 6
r3nderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HugoHiaslCommented:
SELECT toolnumber, MAX(ofuploadtime) as MaxUploadTime
FROM temp_lkltn
GROUP by toolnumber
0
Duy PhamFreelance IT ConsultantCommented:
You should find MAX(uploadtime) per toolnumber first in an inner query, and then use that to get related data by joining with the same table in outer query.

SELECT   tbl.*,  innerQuery.maxofuploadtime
FROM     temp_lkltn tbl
    INNER JOIN (
         SELECT   toolnumber, MAX(uploadtime) as maxofuploadtime
         FROM     temp_lkltn
         GROUP BY  toolnumber
    ) innerQuery ON innerQuery.toolnumber = tbl.toolnumber and tbl.uploadtime = innerQuery.maxofuploadtime

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
SELECT  DISTINCT toolnumber,
       lastoflocation as lastoflocation,
       (select MAX(maxofuploadtime) from temp_lkltn t2 where t2.toolnumber = t1.toolnumber) as maxofuploadtime,
       jobtype,
       company,
       rig,
       rignumber,
       wellname,
       city,
       state
FROM   temp_lkltn t1
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

r3nderAuthor Commented:
Duy it works but it only has toolnumber and maxofuploadtime - all the other columns are null
0
PortletPaulEE Topic AdvisorCommented:
NEVER use "SELECT DISTINCT" with GROUP BY is a single query*

GROUP BY already produces unique rows, "select distinct" on top of that is just a waste of resources.

also see: Select Distinct is returning duplicates ...

For this need I would suggest using row_number()
SELECT
    *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY toolnumber ORDER BY uploadtime DESC) AS rn

      , toolnumber, lastoflocation, uploadtime, jobtype, company, rig, rignumber, wellname, city, state
    FROM temp_lkltn
    ) AS t
WHERE rn = 1

Open in new window


----
* one "select" = "single query"
0
Duy PhamFreelance IT ConsultantCommented:
@r3nder:  It sounds strange, since you can get maxofuploadtime for a toolnumber, meaning that there a record exists with uploadtime = maxofuploadtime of that toolnumber.

Could you re-paste your new query or send me some example data to try?
0
r3nderAuthor Commented:
@Duy and data is attacheddata.csv
 query += " SELECT   tbl.*,  innerQuery.maxofuploadtime ";
                query += " FROM     Temp_LKLTN tbl ";
                query += " INNER JOIN ( ";
                query += "  SELECT   toolnumber, MAX(maxuploadtime) as maxofuploadtime ";
                query += " FROM     Temp_LKLTN ";
                query += " GROUP BY  toolnumber ";
                query += " ) innerQuery ON innerQuery.toolnumber = tbl.toolnumber and tbl.maxofuploadtime = innerQuery.maxofuploadtime ";

Open in new window

0
Duy PhamFreelance IT ConsultantCommented:
I tried your data with the query, and it seemed to be fine (see below image).

Sample Query Result
There was one error in your posted query:

MAX(maxuploadtime) as maxofuploadtime

Open in new window


it should be

MAX(maxofuploadtime) as maxofuploadtime

Open in new window


On the other hand, for the records having JOBTYPE = 'Assembly', most of other fields are NULL. So maybe it caused confusion.
0
r3nderAuthor Commented:
here is what I am getting with the same querydata
0
r3nderAuthor Commented:
I think it is because the Jobtype is not showing up
0
Duy PhamFreelance IT ConsultantCommented:
My result is same as yours. Nothing is wrong here, just that your table contains many records with NULL in most of the fields (records with JOBTYPE = 'Assembly'). You can see that beside toolnumber and maxofuploadtime, lastoflocation and jobtype also have values.
0
r3nderAuthor Commented:
thanks Duy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.