Same query, with or without condition. - sql-server

I want to write a query that selects rows by condition, but if there is no response for that condition, the code should select the same columns, but without the condition.
What the right way to do this? Thanks!
This is my example - select this:
select top 1 *
from tbl
where isActive = 1
but if there is no response, select this instead:
select top 1 *
from tbl
Note that the query is big and complex, so I prefer not to select one and then select the second one, if the first one is null. Also because I have a union after this and it throws an error with this syntax.

Assuming your query is a select top x ... query I would simply use the order by as suggested by Peter's answer. Assuming it's not involving top x, since you wrote your actual query is big and complicated, you can use a common table expression.
The idea is that you encapsulate the big and complex query inside the cte, but instead of writing the where clause to filter out records, you use a case expression to return 1 or 0 if the condition is true or false for each record.
Then you select from that cte where either the case expression results with 1 or there are no records in the cte where the case expression results with 1.
Create and populate sample table (Please save us this step in your future questions)
DECLARE #T AS TABLE
(
Id int identity(1,1),
IsActive bit
)
INSERT INTO #T VALUES
(1),(1),(NULL),(1),(1),(NULL),
(1),(1),(NULL),(1),(1),(NULL),
(1),(1),(NULL),(1),(1),(NULL),
(1),(1),(NULL),(1),(1),(NULL)
The common table expression:
;WITH CTE AS
(
SELECT Id, IsActive,
CASE WHEN IsActive = 1 THEN 1
ELSE 0
END As FoundRecords
FROM #T
)
The query:
SELECT Id, IsActive
FROM CTE
WHERE FoundRecords = 1
OR NOT EXISTS
(
SELECT 1
FROM CTE
WHERE FoundRecords = 1
)
Results:
Id IsActive
1 True
2 True
4 True
5 True
7 True
8 True
10 True
11 True
13 True
14 True
16 True
17 True
19 True
20 True
22 True
23 True
You can see a live demo on rextester

The simplest way is to not use where isActive = 1 but instead order by isActive in descending order:
select top 1 *
from tbl
order by isActive desc
You might even want to consider adding additional ordering fields (e.g. id, name, date), because without that the resulting item could be unpredictable.

You can use the IF-Else condition, right?
DECLARE #result INT
SET #result = CONVERT(INT, (SELECT COUNT(*) FROM tbl WHERE isActive = 1))
IF (#result > 0)
BEGIN
select top 1 * from tbl where isActive = 1;
END
ELSE
BEGIN
select top 1 * from tbl;
END

Related

Is there a way to add a logical Operator in a WHERE clause using CASE statements? - T-SQL

I searched the web but cannot find a solution for my problem (but perhaps I am using the wrong keywords ;) ).
I've got a Stored Procedure which does some automatic validation (every night) for a bunch of records. However, sometimes a user wants to do the same validation for a single record manually. I thought about calling the Stored Procedure with a parameter, when set the original SELECT statement (which loops through all the records) should get an AND operator with the specified record ID. I want to do it this way so that I don't have to copy the entire select statement and modify it just for the manual part.
The original statement is as follows:
DECLARE GenerateFacturen CURSOR LOCAL FOR
SELECT TOP 100 PERCENT becode, dtreknr, franchisebecode, franchisenemer, fakgroep, vonummer, vovolgnr, count(*) as nrVerOrd,
FaktuurEindeMaand, FaktuurEindeWeek
FROM (
SELECT becode, vonummer, vovolgnr, FaktuurEindeMaand, FaktuurEindeWeek, uitgestfaktuurdat, levdat, voomschrijving, vonetto,
faktureerperorder, dtreknr, franchisebecode, franchisenemer, fakgroep, levscandat
FROM vwOpenVerOrd WHERE becode=#BecondeIN AND levdat IS NOT NULL AND fakstatus = 0
AND isAllFaktuurStukPrijsChecked = 1 AND IsAllFaktuurVrChecked = 1
AND (uitgestfaktuurdat IS NULL OR uitgestfaktuurdat<=#FactuurDate)
) sub
WHERE faktureerperorder = 1
GROUP BY becode, dtreknr, franchisebecode, franchisenemer, fakgroep, vonummer, vovolgnr,
FaktuurEindeMaand, FaktuurEindeWeek
ORDER BY MIN(levscandat)
At the WHERE faktureerperorder = 1 I came up with something like this:
WHERE faktureerperorder = 1 AND CASE WHEN #myParameterManual = 1 THEN vonummer=#vonummer ELSE 1=1 END
But this doesn't work. The #myParameterManual indicates whether or not it should select only a specific record. The vonummer=#vonummer is the record's ID. I thought by setting 1=1 I would get all the records.
Any ideas how to achieve my goal (perhaps more efficient ideas or better ideas)?
I'm finding it difficult to read your query, but this is hopefully a simple example of what you're trying to achieve.
I've used a WHERE clause with an OR operator to give you 2 options on the filter. Using the same query you will get different outputs depending on the filter value:
CREATE TABLE #test ( id INT, val INT );
INSERT INTO #test
( id, val )
VALUES ( 1, 10 ),
( 2, 20 ),
( 3, 30 );
DECLARE #filter INT;
-- null filter returns all rows
SET #filter = NULL;
SELECT *
FROM #test
WHERE ( #filter IS NULL
AND id < 5
)
OR ( #filter IS NOT NULL
AND id = #filter
);
-- filter a specific record
SET #filter = 2;
SELECT *
FROM #test
WHERE ( #filter IS NULL
AND id < 5
)
OR ( #filter IS NOT NULL
AND id = #filter
);
DROP TABLE #test;
First query returns all:
id val
1 10
2 20
3 30
Second query returns a single row:
id val
2 20

Performance issue with larger resultsets MSSQL

I currently have a stored procedure in MSSQL where I execute a SELECT-statement multiple times based on the variables I give the stored procedure. The stored procedure counts how many results are going to be returned for every filter a user can enable.
The stored procedure isn't the issue, I transformed the select statement from te stored procedure to a regular select statement which looks like:
DECLARE #contentRootId int = 900589
DECLARE #RealtorIdList varchar(2000) = ';880;884;1000;881;885;'
DECLARE #publishSoldOrRentedSinceDate int = 8
DECLARE #isForSale BIT= 1
DECLARE #isForRent BIT= 0
DECLARE #isResidential BIT= 1
--...(another 55 variables)...
--Table to be returned
DECLARE #resultTable TABLE
(
variableName varchar(100),
[value] varchar(200)
)
-- Create table based of inputvariable. Example: turns ';18;118;' to a table containing two ints 18 AND 118
DECLARE #RealtorIdTable table(RealtorId int)
INSERT INTO #RealtorIdTable SELECT * FROM dbo.Split(#RealtorIdList,';') option (maxrecursion 150)
INSERT INTO #resultTable ([value], variableName)
SELECT [Value], VariableName FROM(
Select count(*) as TotalCount,
ISNULL(SUM(CASE WHEN reps.ForRecreation = 1 THEN 1 else 0 end), 0) as ForRecreation,
ISNULL(SUM(CASE WHEN reps.IsQualifiedForSeniors = 1 THEN 1 else 0 end), 0) as IsQualifiedForSeniors,
--...(A whole bunch more SUM(CASE)...
FROM TABLE1 reps
LEFT JOIN temp t on
t.ContentRootID = #contentRootId
AND t.RealEstatePropertyID = reps.ID
WHERE
(EXISTS(select 1 from #RealtorIdTable where RealtorId = reps.RealtorID))
AND (#SelectedGroupIds IS NULL OR EXISTS(select 1 from #SelectedGroupIdtable where GroupId = t.RealEstatePropertyGroupID))
AND (ISNULL(reps.IsForSale,0) = ISNULL(#isForSale,0))
AND (ISNULL(reps.IsForRent, 0) = ISNULL(#isForRent,0))
AND (ISNULL(reps.IsResidential, 0) = ISNULL(#isResidential,0))
AND (ISNULL(reps.IsCommercial, 0) = ISNULL(#isCommercial,0))
AND (ISNULL(reps.IsInvestment, 0) = ISNULL(#isInvestment,0))
AND (ISNULL(reps.IsAgricultural, 0) = ISNULL(#isAgricultural,0))
--...(Around 50 more of these WHERE-statements)...
) as tbl
UNPIVOT (
[Value]
FOR [VariableName] IN(
[TotalCount],
[ForRecreation],
[IsQualifiedForSeniors],
--...(All the other things i selected in above query)...
)
) as d
select * from #resultTable
The combination of a Realtor- and contentID gives me a set default set of X amount of records. When I choose a Combination which gives me ~4600 records, the execution time is around 250ms. When I execute the sattement with a combination that gives me ~600 record, the execution time is about 20ms.
I would like to know why this is happening. I tried removing all SUM(CASE in the select, I tried removing almost everything from the WHERE-clause, and I tried removing the JOIN. But I keep seeing the huge difference between the resultset of 4600 and 600.
Table variables can perform worse when the number of records is large. Consider using a temporary table instead. See When should I use a table variable vs temporary table in sql server?
Also, consider replacing the UNPIVOT by alternative SQL code. Writing your own TSQL code will give you more control and even increase performance. See for example PIVOT, UNPIVOT and performance

Sequentially sum values restarting sum total on every change

I have these values in a column:
1
1
1
-1
-1
1
1
1
1
1
-1
1
I need a query that will return the first sequential sum of values and then the next one and so forth, every time the value differs from the one before a new sum will be performed.
The result of these sequential sums, according to the example, will be the following:
3
-2
5
-1
1
What will be the best approach to do this in terms of speed and resource usage, a loop through each value and calculate each sum or can this be done in one query?
Any ideas please?
---------- EDITION ------------
I managed to add the following column using the row_number over partition by:
COL A COL B
1 1
2 1
3 1
1 -1
2 -1
1 1
2 1
3 1
.... and so forth
Can this help in any way?
--------- Second Edition ---------
I have this:
SELECT ROW_NUMBER() OVER (PARTITION BY T.RES ORDER BY T.ID_Test_Case DESC) AS GRP,
T.RES,
T.ID_Test_Case
FROM (
SELECT TEST_CASE.ID_Test_Case,
(CASE WHEN TEST_RESULT.Name = TR2.Name THEN 1 ELSE -1 END) AS RES
FROM (Join from all tables needed)
WHERE (All Conditions) ) T
ORDER BY T.ID_Test_Case DESC
)
This is where I'm retrieving the values 1 and -1, which will always be ordered the same because of the order by clause. I can only use the Test_Case.ID to order and is a unique field... Hope this helps
Use a recursive CTE:
DECLARE #tmp TABLE
(
RowID int IDENTITY(1,1),
Value int
)
INSERT INTO #tmp
VALUES (1),(1),(1),(-1),(-1),(1),(1),(1),(1),(1),(-1),(1)
;WITH
Groups AS
(
SELECT RowID As GroupID,
RowID As RowID,
Value As Value
FROM #tmp
WHERE RowID = 1
UNION ALL
SELECT CASE
WHEN this.Value = prev.Value THEN prev.GroupID
ELSE this.RowID
END As GroupID,
this.RowID As RowId,
this.Value AS Value
FROM #tmp this
INNER JOIN Groups prev ON this.RowID = prev.RowID + 1
)
SELECT SUM(Value)
FROM Groups
GROUP BY GroupID
Explanation:
What you want is to segregate rows into groups of sequentially equal values. The RowID here is used as the row number.
The CTE starts with RowID = 1, creating a new "group"
The UNION ALL recursively adds in RowID = 2, 3, 4 etc. With each iteration, it considers if its value is equal to the previous row's value. If it is, it uses the same GroupID as the previous row. If not, a new group is started with its RowID.
We now simply need to sum up the value in each group to get what you want.
To see how the internals of this work, replace the final SELECT with SELECT * FROM Groups
Should be self-explanatory. Not elegant, but will get the job done.
Create Table _Numbercolumn (val int)
Create Table _RandValues ( random int)
Create Table _Sum([sum] int)
insert into _RandValues values(1)
insert into _RandValues values(-1)
while ( select COUNT(val) from _Numbercolumn ) < 100
BEGIN
Insert into _numbercolumn select top 1 random from _RandValues order by NEWID()
END
DECLARE Number_Cursor CURSOR FOR SELECT val from _Numbercolumn
DECLARE #CurrentSum int, #CursorVal int
OPEN Number_Cursor;
Set #CurrentSum = 0
FETCH NEXT FROM Number_Cursor into #CursorVal;
WHILE ##FETCH_STATUS = 0
BEGIN
IF SIGN(#CurrentSum) <> SIGN(#CursorVal)
BEGIN
INSERT INTO _Sum Values(#CurrentSum)
SET #CurrentSum = #CursorVal
END
ELSE
SET #CurrentSum = #CurrentSum + #CursorVal
FETCH NEXT FROM Number_Cursor into #CursorVal;
END;
CLOSE Number_Cursor;
DEALLOCATE Number_Cursor;
GO
Select * from _Sum
Here is the same set as yours with unique ordering:
SELECT *
INTO #t
FROM (VALUES (1,1),(2,1),(3,1),(4,-1),(5,-1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,-1),(12,1)) AS t(RowNbr, VALUE);
Now, using the above, you can do it in a single query (there is probably an even better way of doing it, but this should suffice):
;WITH ChangingRows AS (
SELECT t1.RowNbr, t1.Value, ROW_NUMBER() OVER (ORDER BY t1.RowNbr) AS Ord
FROM #t t1
LEFT JOIN #t t2 ON t1.RowNbr = t2.RowNbr - 1
WHERE t1.Value != ISNULL(t2.Value, 0)
)
SELECT (c1.RowNbr - ISNULL(c2.RowNbr, 0)) * c1.Value as Total
FROM ChangingRows c1
LEFT JOIN ChangingRows c2 ON c2.Ord = c1.Ord - 1;

I need some way to get true or false if the row contents of two simple one column tables are the same.

I have two tables in a SQL Server Stored Procedure:
select * from #tbl
select * from #Answers
1 0
2 1
3 0
4 0
5 0
1 0
1 0
1 1
1 1
1 0
CREATE TABLE #tbl (
[Id] INT IDENTITY(1,1),
[Correct] BIT
)
CREATE TYPE [dbo].[AnswerList] AS TABLE (
[Id] INT NULL,
[Response] BIT NULL);
How can I compare the contents of these tables and come up with a return code of true or false to show if the Id.Correct matches with the Id.Reponse for each row.
Note that the tables will always have the same number of rows and there will be no null values so no need to check for that.
Also note that I just need one output to show if all the rows match or not.
Try this:
IF Exists (Select t.id
From #tbl as t
Left Join #Answers a on t.id = a.id
Where a.id is null or a.Response <> t.Correct) Begin
Print 'False'
End Else Begin
Print 'True'
End

How do i check if something exist without using count(*) … limit 1

My code is SELECT COUNT(*) FROM name_list WHERE [name]='a' LIMIT 1
It appears there is no limit clause in SQL Server. So how do i say tell me if 'a' exist in name_list.name?
IF EXISTS(SELECT * FROM name_list WHERE name = 'a')
BEGIN
-- such a record exists
END
ELSE
BEGIN
-- such a record does not exist
END
Points to note:
don't worry about the SELECT * - the database engine knows what you are asking
the IF is just for illustration - the EXISTS(SELECT ...) expression is what answers your question
the BEGIN and END are strictly speaking unnecessary if there is only one statement in the block
COUNT(*) returns a single row anyway, no need to limit.
The ANSI equivalent for LIMIT is TOP: SELECT TOP(1) ... FROM ... WHERE...
And finally, there is EXISTS: IF EXISTS (SELECT * FROM ... WHERE ...).
The TOP clause is the closest equivalent to LIMIT. The following will return all of the fields in the first row whose name field equals 'a' (altough if more than one row matches, the row that ets returned will be undefined unless you also provide an ORDER BY clause).
SELECT TOP 1 * FROM name_list WHERE [name]='a'
But there's no need to use it if you're doing a COUNT(*). The following will return a single row with a single field that is number of rows whose name field eqals 'a' in the whole table.
SELECT COUNT(*) FROM name_list WHERE [name]='a'
IF (EXISTS(SELECT [name] FROM name_list where [name] = 'a'))
begin
//do other work if exists
end
You can also do the opposite:
IF (NOT EXISTS(SELECT [name] FROM name_list where [name] = 'a'))
begin
//do other work if not exists
end
No nono that is wrong.
First there is top, so you have to say something like:
select top 1 1 from name_list where [name]='a'
You'll get a row with only a unnamed field with 1 out of the query if there is data, and no rows at all if there is no data.
This query returns exactly what you intended:
SELECT TOP 1 CASE WHEN EXISTS(SELECT * WHERE [name] = 'a') THEN 1 ELSE 0 END FROM name_list

Resources