Select only one from many

Question

0.00/5 (No votes)

See more:

I've had similar problems as this before and while scouring my own questions at ask-a-question sites I only come part to a solution.

I have the following query here:

SQL

select fcontainer.id, FlaggId, sum(FContainerRow.Units) as units ,fcontainerplats.x,FContainerPlats.y,FContainerHylla.Name,
tempo.temponr,produkter.Artikelnummer,produkter.Produktnamn

from fcontainer inner join fcontainerrow on fcontainer.id = FContainerRow.FContainerId 
inner join fcontainerplats on fcontainer.PlatsId = FContainerPlats.id
inner join FContainerHylla on fcontainerplats.HyllId = FContainerHylla.Id
inner join tempo on fcontainerrow.ExtKeyId = tempo.lagernr
inner join produkter on produkter.Produktnr = tempo.produktnr

where ExtKeyName = 'Lager' and fcontainer.PlatsId > 0
group by fcontainer.id, flaggid, fcontainerplats.x,FContainerPlats.y, FContainerHylla.Name,tempo.temponr,produkter.Artikelnummer,produkter.Produktnamn

My problem is that multiple entries in tempo can have the same value in tempo.lagernr which means that for one fcontainer.id I get multiple rows.

I don't care which row of these duplicates I select as long as there is only one row per fcontainer.id in my final query. I've been tin kering with row_number() over partion as well as using CTE but I just cant get it right.

When "counting" the rows using rownumber I'm stuck at selecting rn = 1 which only returns those rows where there is no duplicate.

Posted 25-Oct-15 21:19pm

Member 11683251

Add a Solution

Comments

Tomas Takac 26-Oct-15 4:01am

Remove tempo.temponr from GROUP BY and use MAX(tempo.temponr) in SELECT clause.

Member 11683251 26-Oct-15 4:07am

Still the same results, I realized that it's not tempo.temponr that exists duplicates from but it's the tempo.lagernr. Multiple entries in tempo can have the same lagernr. But select max(tempo.lagernr) instead still generates duplicates.

Tomas Takac 26-Oct-15 6:21am

The reason most probably being the join chain fcontainerrow -> tempo -> produkter. So if tempo duplicates the rows then this "duplication" is propagated to produkter. I don't know enough about you data structure to help here. I guess you need to prepare (pre-aggregate) data in tempo & produkter so it has only one row per a tempo.lagernr and then use this data in you join.

Member 11683251 26-Oct-15 4:12am

After thinking this might help better: Muliple rows in fcontainerrow can have the same fcontainerid. Which Is why I sum fcontainerrow.units. Fcontainerrow.fcontainerid = fcontainer.id.

Each of these rows contain a column called extkeyid. The value here maps to tempo.lagernr in which multiple entries in tempo can have the same value here.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2015-10-28T05:17:00

Solution 1

This should work.

SQL

WITH cte AS (
    SELECT
            fcontainer.id
           ,FlaggId
           ,Sum(FContainerRow.Units) AS units
           ,fcontainerplats.x
           ,FContainerPlats.y
           ,FContainerHylla.Name
           ,tempo.temponr
           ,produkter.Artikelnummer
           ,produkter.Produktnamn
           ,ROW_NUMBER() OVER (PARTITION BY fcontainer.id) rn
    FROM    fcontainerrow
    JOIN    fcontainer ON  FContainerRow.FContainerId = fcontainer.id 
    JOIN    fcontainerplats ON  fcontainer.PlatsId = FContainerPlats.id
    JOIN    FContainerHylla ON  fcontainerplats.HyllId = FContainerHylla.Id
    JOIN    Tempo ON  fcontainerrow.ExtKeyId = tempo.lagernr
    JOIN    produkter ON  produkter.Produktnr = tempo.produktnr
    WHERE   ExtKeyName = 'Lager'
        AND fcontainer.PlatsId > 0
    GROUP BY fcontainer.id
            ,flaggid
            ,fcontainerplats.x
            ,FContainerPlats.y
            ,FContainerHylla.Name
            ,tempo.temponr
            ,produkter.Artikelnummer
            ,produkter.Produktnamn
    )
SELECT   
        fcontainer.id
       ,FlaggId
       ,units
       ,x
       ,y
       ,Name
       ,temponr
       ,Artikelnummer
       ,Produktnamn
FROM    cte
WHERE   rn = 1

Posted 28-Oct-15 5:17am

Jörgen Andersson

Updated 28-Oct-15 8:29am

v2

Comments

Richard Deeming 28-Oct-15 13:27pm

In English, please. :)

Jörgen Andersson 28-Oct-15 14:30pm

Sorry 'bout that. I realized he/she is Swedish and my brain swapped languages.
Updated

phil.o 28-Oct-15 14:54pm

5'd
CTEs are really elegant solutions to complex issues.

Jörgen Andersson 29-Oct-15 3:59am

Thanks

Maciej Los 28-Oct-15 15:51pm

5!

Jörgen Andersson 29-Oct-15 3:58am

Thanks

Member 11683251 29-Oct-15 2:03am

Tack :) Managed a work around but after being advised to use CTE's to solve other problems I cant help feeling that I actually should have been able to figure this one out. Anyway thanks for the time.

Jörgen Andersson 29-Oct-15 3:58am

It's just about keeping the order of execution in mind.

First the database does all the Joining.
Then all the filtering (Where Clause)
Then Aggregation (Group By)
Filtering on Aggregates (Having Clause)
Analytic Functions
And finally Order by.

This is one of the most powerful aspects of CTEs, they allow you to change the order of execution.

Also note that there's no filtering on Analytic functions, so a CTE is necessary here.

Member 11683251 29-Oct-15 4:04am

That's a really helpful description, one of the main obstacles I tend to run in to is that I wish to solve my queries similarly to normal programming. If this do that, loop this etc. One work around has been in my programs to run multiple queries and then use that data to get what I want.

Jörgen Andersson 29-Oct-15 4:22am

It's odd that you basically can't find any info on that online.
When done correctly there is nothing that is as fast as a database, they are highly optimized for filtering and aggregation.

My rule of thumb is to keep the size of the datasets to a minimum in every step, so if you can use CTEs to filter and aggregate before joining you can gain several magnitudes in performance.