First amongst many

If your SQL query returns a list, but each item in the list is represented several times because of another item in the query AND YOU WANT the second item, you could try to return just the FIRST of the second items.

We have courses and some have several faculty.  We want one/any one of the faculty for the course.  The output should be each course one time and just one of the faculty (email?) for each course.

with myCTE
as (select cm.course_id, u.user_id, u.email,
    run_number() over (partition by cm.course_id order by cm.course_i as myList
    from course_main cm
    join course_users cu on cu.crsmain_pk1
    join users u on u.pk1 = cu.users_pk1
    where ....);

Select * from myCTE order by myList

This will have the effect, in the CTE (Common table expression) of creating a column called row_number (aliased to myList), which will restart for each change in cm.course_id.  Order by myList, and you have all the first-items in the top of the table.

Of course, there is no need to display the myLists larger than 1 , or even that new variable, which is generated in the CTE table. (NB – gotcha if you forget to put a semi-colon after the close of the CTE before the select statement.

 course_id,user_id, email from myCTE where myList = 1
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.