Skip to content

Tag: sql-server

How to display rows with no count aggregate result?

I have a table which contains Job, City and PersonName (who works in that city). I want to return all jobs and cities even if there is no one working in that city. This is a simple example : Creation of the table : My Query My result But I would like to have : I have no clue how

Add check constraint related to other column

My table structure is as below I want to add a constraint to this table where only if IsActive=1, GoSequence value can be change. If IsActive=0, GoSequence stays 0. Any help is appreciated. Thanks! Answer Probably the simplest way of expressing that is that either GoSequence must be 0 or IsActive must be 1. S…

Convert rows to columns using PIVOT

I have a table named AmountList in SQL Server : where Primary key is ” UniqueCol1, UniqueCol2, AmountID ” and on the basis of AmountID I want to Pivot the results as: I am facing the challenge using Pivot because only Aggregate functions can be used but I need actual values of the fields. UPDATE: …

Return the second newst record of a group sql

I have a table like the following: how can I get this: the result should be the second newest record of a coworker if there’s more than one, or the only present Thanks for any help! Answer Try the following with window function row_number. Here is the demo. Output:

CTE Recursive Query to Grandparents

I have following table: What I need to get is SELECT query that will return only rows that have ISDELETED 0 and ISINEDIT 0 and whose parents or grandparents are 0 as well I currently have: But for some reason it returns double rows Answer You need to add the same isdeleted = 0 AND isinedit = 0 predicate to