Re: Copilot generated SQL

[Daniel Gross <daniel@xxxxxxxx>]

Well the OVER() window function isn't so new to SQL - but many don't use it by now. Historically you had to jump through some "sub-select-loops" to make the same without OLAP Window functions. 

Nice to see, that Copilot does produce somehow modern SQL. 

Kind regards,
Daniel 


> Am 17.02.2025 um 17:19 schrieb Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>:
>
> I currently have this SQL statement:
> select vi.*
>  from QSYS2.ASP_VARY_INFO vi
> Order by IASP_NAME, START_TIMESTAMP desc
> Fetch first 1 row only ;
>
> We have a few systems with more than one iASP, so I needed to modify that statement to get all of the iASPs that meet the criteria (first by descending START_TIMESTAMP).  It's been years since I did something similar, so I thought I'd give Copilot a shot at it.  Here's what it responded with:
>
> WITH RankedRows AS (
>    SELECT vi.*, ROW_NUMBER() OVER (PARTITION BY IASP_NAME ORDER BY START_TIMESTAMP DESC) AS rn
>    FROM QSYS2.ASP_VARY_INFO vi
> )
> SELECT * FROM RankedRows
>   WHERE rn = 1
>   ORDER BY IASP_NAME ;
>
> This does work, but I'm wondering whether this is the preferred method, as I don't recall using PARTITION BY before to do this.  Thoughts?
>
> - Dan Bale
> *** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.