October 28, 2020 at 8:26 pm
.
October 29, 2020 at 2:42 pm
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
October 29, 2020 at 3:02 pm
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;
October 29, 2020 at 8:51 pm
>> 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