• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

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
0
r3nder
Asked:
r3nder
1 Solution
 
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
r3nderAuthor Commented:
Duy it works but it only has toolnumber and maxofuploadtime - all the other columns are null
0
 
PortletPaulfreelancerCommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now