How do I limit the number of rows returned by this LEFT JOIN to one?
join, limit, oracle, sql
Solution
If oracle supports row number (partition by) you can create a sub query selecting where row equals 1.
SELECT * FROM table1
LEFT JOIN
(SELECT *
FROM (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY assignmentgroup ORDER BY assignmentgroup) AS Seq
FROM table2) a
WHERE Seq = 1) v
ON assignmet = v.assignmentgroup
Problem
So I think I've seen a solution to this however they are all very complicated queries. I'm in oracle 11g for reference. What I have is a simple one to many join which works great however I don't need the many. I just want the left table (the one) to just join any 1 row which meets the join criteria...not many rows. I need to do this because the query is in a rollup which COUNTS so if I do the normal left join I get 5 rows where I only should be getting 1. So example data is as follows: ``` TABLE 1: ------------- TICKET_ID ASSIGNMENT 5 team1 6 team2 TABLE 2: ------------- MANAGER_NAME ASSIGNMENT_GROUP USER joe team1 sally joe team1 stephen joe team1 louis harry team2 ted harry team2 thelma ``` what I need to do is join these two tables on ASSIGNMENT=ASSIGNMENT_GROUP but only have 1 row returned. when I do a left join I get three rows returned beaucse that is the nature of hte left join