Getting the top unique value by group

  • .

    • This topic was modified 4 years ago by  David.Toews 95002. Reason: Would like topic removed, description unclear and posted in frustration
  • ever considered sequences ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This should get you going in the right direction

    ;WITH cteBase AS (
    SELECT TABLE1.ID, TABLE2.Processingnumber
    , rn = ROW_NUMBER() OVER (PARTITION BY TABLE1.ID ORDER BY TABLE2.Processingnumber)
    FROM TABLE1
    JOIN TABLE2 ON ...
    WHERE ...
    )
    SELECT cte.ID, cte.Processingnumber
    FROM cteBase AS cte
    WHERE cte.rn = 1;
  • >> We have two tables, and are trying to populate a third table by selecting the first available processing number for each ID. The two tables are joined like this: <<

    Where is the DDL for these two nameless tables (no, putting a number after the word "table" is not a name. It's a design flaw called a tibble) Why do you believe that there is such a thing as a universal, generic, Kabbalah "id" in RDBMS (an identifier has to be the identifier of something in particular). We don't even know the data types, much less the keys in these two tables! We have no idea how the join was done.

    Why do you think that this shows good netiquette? Does your boss make you work from things like this? Or do you just always shovel most important parts of the design off on somebody else?

    Why are you populating another table? SQL can use a VIEW, but your mindset is still stuck in the days of punch cards and magnetic tape, when you had to physically materialize a result set.

    >> Any help would be appreciated <<

    We feel the same way 🙁

    CREATE TABLE Foobars

    (foobar_id CHAR(4) NOT NULL,

    process_nbr INTEGER NOT NULL,

    PRIMARY KEY (foobar_id, process_nbr);

    CREATE VIEW First_Foobars (foobar_id, process_nbr_min)

    AS

    SELECT foobar_id, MIN(process_nbr)

    FROM Foobars

    GROUP BY foobar_id;

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply