Hot answers tagged

6

Rewrite your query such a way: SELECT a.*, x.cnt FROM #temp a CROSS APPLY ( SELECT COUNT(VAL) AS cnt FROM #temp b WHERE a.val <= b.val ) x --WHERE x.cnt = 1 If you uncomment the where clause you would get 333 | 1 as a result. You request a row from the outer table which doesn't have duplicates or bigger values.


3

You could try applying the filter (WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104')) Directly on the index instead of it being implemented as a filter further in the execution plan. You can do this by not applying functions on columns in your where clause and adding an index. The group by is also ...


3

tablesample is an "attribute" of a table, not a query. So you need to write it write after the table name: select * from customers tablesample system (1) where last_name = 'powell'; Note that the where clause will be applied after the table has been sampled. It will not return 1% of all customers that have the last name 'powell'. But instead it will apply ...


3

You could see your inner count query Select COUNT(VAL) from #temp b where a.val <= b.val; as (Select COUNT(VAL) from #temp b where 333 <= 333,222,111) = 1 (Select COUNT(VAL) from #temp b where 222 <= 333,222,111) = 2 (Select COUNT(VAL) from #temp b where 111 <= 333,222,111) = 3 Showing that only 333 from #temp a has one match, as it ...


2

Top is only giving you one record because you're asking for the top '1' row. If you want all, just change it to: select (md2.table2) from table2 md2 where md2.EFFECTIVEDATE < '2019-12-05' and md2.key2 in (select key2 from table1) order by EFFECTIVEDATE desc


2

As mustaccio pointed out this is a "gaps and islands" problem: declare @date date = '2020-01-16'; with ct as ( /* First you must detect the gaps, filtering those rows lesser than the selected date */ select OrderDate, case when datediff(day, coalesce(lag(OrderDate) over (order by OrderDate), ...


1

WITH cte1 AS ( SELECT *, CASE WHEN name = LAG(name) OVER (ORDER BY id) THEN 0 ELSE 1 END the_same FROM test ), cte2 AS ( SELECT *, SUM(the_same) OVER (ORDER BY id) group_num FROM cte1 ) SELECT DISTINCT name, FIRST_VALUE(value) OVER (...


1

use aliases for your table especially when you self join SELECT a1.id, a1.display_name, a1.agents_name, a1.estates_name, t.agents_name, t.estates_name FROM author_table a1 INNER JOIN( SELECT agents_name.lestates_name FROM author_table GROUP BY agents_name and estates_name HAVING COUNT(agents_name) >1 AND COUNT(estates_name)>1 )temp t ON a1.agents_name ...


1

You can use EXCEPT to remove the complementing rows. SELECT friend1,friend2 FROM friends WHERE friend1=2 or friend2=2 EXCEPT SELECT friend2,friend1 FROM friends WHERE friend1=2 or friend2=2; But maybe using a CTE will perform better. WITH buddies AS ( SELECT friend1,friend2 FROM friends WHERE friend1=2 or friend2=2 ) SELECT friend1,...


1

If you need a more dynamic function that wouldn't require you to write logic for each year, you could use this CASE WHEN statement below that will work for any year given a set FY (in this Oct. 1 is the start of the FY). Essentially it, reads the month of the date field and if it's from Jan - Sept, then it extracts the year as is from the date field and ...


Only top voted, non community-wiki answers of a minimum length are eligible