spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Top 1 with where clause - ignore where clause if no record is found


You want to prioritize the results, with Canceled = 0 first and then any other row. You can do this with a single outer apply:

select a.Description, a.Date,
a.Canceled
from SomeOtherTable t outer apply
     (select top 1 * 
      from activities a 
      where t.id = a.SomeForeignKey
      order by (case when a.canceled = 0 then 1
else 0 end) desc, a.Date
     ) a;

I would normally put similar logic in a row_number() calculation, but outer apply works just as well.

EDIT:

For completeness and comparison, here is the row_number() method:

select a.Description, a.Date,
a.Canceled
from SomeOtherTable t left join
     (select a.*,
             row_number() over (partition by
a.SomeForeignKey
                                order by (case
when a.canceled = 0 then 1 else 0 end) desc,
a.Date
                               ) as seqnum
      from activities a 
     ) a
     on t.id = a.SomeForeignKey and seqnum = 1;

In general, I think the apply method is a wee bit faster -- essentially, it stops at the first matching row and doesn't need to keep processing for a give t.id.


Categories : SQL

Related to : Top 1 with where clause - ignore where clause if no record is found
Why does my AND or WHERE clause not work?
The result of a comparison with NULL is also NULL, so as a WHERE condition, you implicitly throw away the rows where that was no record in t4, instead, you can COALESCE to always have a non-null value to do your comparison, and you'll always get a true result. So something like: SELECT t1.col1, t2.col2, t3.col3, t4.col4 FROM t2ble1 t1 LEFT JOIN

Categories : Mysql
SQL: using clause with "ORDER BY"
You can use order by field() select * from table_name order by field(country_code,'GB') desc,country_code DEMO

Categories : Mysql
Why does the HAVING clause need to come after the GROUP-BY?
The reason is because the SQL will group records before it evaluates the HAVING clause. It is mainly the SQL syntax, however it makes intuitive sense, as Jeroen points out : HAVING applies to the result of your grouping, so in this particular, unusual case, SQL is following the chronological order of the query steps. 10 Tips for Sorting SQL - TechRepublic

Categories : SQL
SQl if statement in where clause
We can study the CASE syntax from here , it would be, roughly (I am not acquainted with MySQL date formats , so please look that up ) SELECT AgentID, PaymentAmount FROM Table A WHERE CASE DATEOFPAYMENT WHEN subdate(currentDate, 1) THEN PaymentAmount WHEN month() THEN PaymentAmount WHEN year() THEN PaymentAmount ELSE 0 END GROUP BY CollectorNumber ORDER BY CollectorNumber, LastName

Categories : Mysql
TSQL how to use if else in Where clause
Just use conditional logic for this: where . . . and ((@IsApprovedDate = 1 and c.ApprovedDate >= @startDay and c.ApprovedDate <= @endDay) or (@IsCatchDate = 1 and c.catchDate >= @startDay and c.catchDate <= @endDay) ) EDIT: I would actually write this as: where . . . and ((@IsApprovedDate = 1 and c.ApprovedDate >= @startDay and c.ApprovedDate < datea

Categories : SQL
Recently Add
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
SQL Server 2008 Agent jobs. Two procedures and a view
Can't create table in Oracle Developer
Trigger error "ORA-01403: no data found" when It is not fire
SQL: alternatives and substitutions for GROUPING SETS and PIVOT
Excel Vlookup too Slow
Is WHERE field IS NULL quicker than >0
BFILE error on oracle
SQL Server : Select from two tables with default value 0
Use of uninitialized value after fetchrow()
Why did this UPDATE change all rows instead of just one row?
Oracle / SQL Counting Different values from one Column and storing it in a variable
How can I calculate the percentage of occurrences of a value in a column?
To compute sum regarding to a constraint
Enterprise Architect - Execute SQL Issue
First business day of the current month - SQL Server
Incorrect no. of rows fetched in SQL
SQL query design with configurable variables
Grouping dates by dateadd and datediff
SQLServer get top 1 row from subquery
SQL Using CONTAINS() doesn't work for AND & OR
SQL Server: Persisting computed column
Improve SQL Server query
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.