0

I have a number of uniquely identified records/rows that are returned as results when selected by their shared identifier. I only want one record/result returned per group. If a given variable belonging to each record is set to a specific value, I want to return that row (which would always be the last instance, if it is set - there is always only one instance of the variable being set for a group of query results). If all the possible rows/results for an instance are reviewed and the variable for that group is not set (meaning the last row returned doesn't have that variable set), then I would want to return the FIRST row result (the first result sorted by a DIFFERENT variable).

so far I have

SELECT table1.var1, table1.var2, table1.var3, table1.var4 etc 
from table1, table2 
where table1.var1 = table2.var1 
  AND (condition that satisfies where the variable is set) 
   OR (condition where the variable isn't set but returns multiple results
       that I need the first instance of, but ONLY IF the first condition
       doesn't exist in the group of results) 
GROUP BY table1.var1, table1.var2 etc 
ORDER BY table1.var1, table1.var2 DESC,           
         table1.variablethatIthoughtwouldsortthesecondconditionbutdoesnt DESC;

I tried using ASC and DESC but the first condition will only be met in the last result, and I would only want the result in the OR condition if the first condition cannot be met - but it has to be the first instance of whatever results match the OR condition.

Any help is appreciated, and I am happy to further clarify if needed.

I'm basically looking for a way to give precedence to the first condition (select the record that whose variable is set if there is one) and select the first instance of the other conditional if it isn't.

In other words, I want any records that meet the first condition (there will only be one per group), and then I also want, from a different group, only the first result per grouping that meets the second condition but whose ID is not in the first group (because the record should only either be in the first group or the second group, not both).

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
user3312062
  • 13
  • 1
  • 4
  • Too many guesses. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Feb 25 '16 at 16:48
  • what table is condition in? My first guess would be to generate a set of data which is jus thte "Max" or min + unique key and join that set back to your table1 or table2. Thus limiting to Just 1 record if it exists. But without stample data, expected results, this would be challenging to do given the above. – xQbert Feb 25 '16 at 16:50
  • You can use `COALESE` in your `ORDER BY` clause: http://stackoverflow.com/questions/4512767/sql-order-by-two-columns-intermixed-not-priority-based – Adrian Lynch Feb 25 '16 at 16:51
  • limiting results to one record isn't the problem. I can't figure out how to check for the second condition to make sure it isn't among the results in the first condition. – user3312062 Feb 25 '16 at 16:52
  • Maybe it would help if I mention that it's possible for both the first condition and the second condition to occur in a grouping of rows, but I'm not sure how to only select the second condition when the first condition is not met in a given grouping. – user3312062 Feb 25 '16 at 16:54

0 Answers0