'How can I avoid duplicate rows from SQL statement?

My problem is, that the following SQL query returns duplicate rows:

select
inv.i4201 as pmid,
inv.i4235 as calPlace,
inv.i4204 as device,
inv.i4203 as type,
inv.i4202 as manufacturer,
inv.i4206 as serialNumber,
cal.c2323 as condition,
inv.i4299 as availability,
inv.i4236 as dateRegistration,
max(cal.c2301) as dateCalibration,
inv.i4229 as interval,
max(cal.c2303) as dateExpiry,
inv.i4228 as intervallType,
loc.l2803 as costCenter,
inv.i4242 as location,
cust.k4601 as CustomerNumber,
cust.k4602 as CustomerName
from
    inventory inv
        join customers cust on inv.ktag = cust.ktag
        join calibration cal on inv.mtag = cal.mtag
        join location loc on inv.mtag = loc.mtag
where   CustomerName like  'xxx%' and device like 'yyy%'

group by
pmid,
calPlace,
device,
type,
manufacturer,
serialNumber,
condition,
availability,
dateRegistration,
interval,
intervallType,
costCenter,
location,
CustomerNumber,
CustomerName

order by pmid;

What do I do to get just one row for each pmid with the latest dateCalibration and/or dateExpiry?

sqldistinctsybase


Solution 1:[1]

I think you might need to investigate further. The real question here is "why are you getting duplicate rows?" Are there duplicate customer records? If so, try using the DISTINCT key word. Since you are joining tables, do some customers have multiple locations? Or multiple calibration records? If so, you will need to determine which records you "want" in your result set. Do you want the first record? Or the latest record? Or is there another condition to determine the data you want to show?

After answering these, then use some aggregate functions such as MAX or MIN or SUM or COUNT etc. It really depends on what data you need to extract.

Solution 2:[2]

Please try this.

select
inv.i4201 as pmid,
max(cal.c2301) as dateCalibration,
from
    inventory inv
        join customers cust on inv.ktag = cust.ktag
        join calibration cal on inv.mtag = cal.mtag
        join location loc on inv.mtag = loc.mtag
where   CustomerName like  'xxx%' and device like 'yyy%'

group by
pmid
order by pmid;

Solution 3:[3]

The following query returns just one record per pmid:

    select
inv.i4201 as pmid,
max(cal.c2301) as dateCalibration,
inv.i4204 as device,
cust.k4602 as CustomerName
from
    inventory inv
        join customers cust on inv.ktag = cust.ktag
        join calibration cal on inv.mtag = cal.mtag
        join location loc on inv.mtag = loc.mtag
where   CustomerName like  'xxx%' and device like 'yyy%'

group by
pmid,
device,
CustomerName
order by pmid;

Meanwhile I solved the problem by exporting the results into a single Postgres table and removing the duplicate rows with a simple max() aggregation function.

Thanks you all for you time to read my posting.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Matt Spinks
Solution 2 Datta
Solution 3 user9056895

Related Questions