Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem with writing a query. Kindly guide... Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 7:17 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:25 AM
Points: 718, Visits: 547
Hi All,

I have RATE and SHIFT columns in a table as mentioned below. When I pass RATE=1 then I get SHIFT = 1,2,3. Now in second step, I need to write a query that will find all rates where atleast these three shifts compulsorily exist (e.g. RATE 3,5 as per below data).

All above work is to be done as a part of a single query and first input of RATE will be dynamic. Below mentioned data is only for example, and real table will be having huge data.

Rate Shift
1 1
1 2
1 3
2 5
3 1
3 2
3 3
8 4
4 1
4 2
5 1
5 2
5 3
5 4
6 1
7 2
8 2

Regards,
Ashish
Post #1498823
Posted Thursday, September 26, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
You have been around here long enough to know that posting some consumable ddl and data is far more likely to get responses.

That being said why is only 3 and 5 returned? What about 1?

Seems to me like all you need is
GROUP BY Col1 
HAVING COUNT(*) >= 3



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1498827
Posted Thursday, September 26, 2013 1:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:06 AM
Points: 115, Visits: 630
This problem is known as Relational Division.

SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @T TABLE (
Rate int NOT NULL,
Shift int NOT NULL,
PRIMARY KEY (Rate, Shift)
);

INSERT INTO @T (
Rate,
Shift
)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 5),
(3, 1),
(3, 2),
(3, 3),
(8, 4),
(4, 1),
(4, 2),
(5, 1),
(5, 2),
(5, 3),
(5, 4),
(6, 1),
(7, 2),
(8, 2);


DECLARE @Rate int = 1;

WITH C1 AS (
SELECT
Shift
FROM
@T
WHERE
Rate = @Rate
)
SELECT
A.Rate
FROM
@T AS A
INNER JOIN
C1 AS B
ON A.Shift = B.Shift
GROUP BY
A.Rate
HAVING
COUNT(*) = (SELECT COUNT(*) FROM C1);
GO

/*

Rate
1
3
5

*/




Post #1499042
Posted Thursday, September 26, 2013 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
Thanks to hunchback for the ddl and data. Here is another way to do the same thing.

select Rate
from @T
cross apply (select count(*) as RowsCount from @t where rate = @Rate) r
group by Rate
having COUNT(*) >= max(r.RowsCount)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1499048
Posted Thursday, September 26, 2013 7:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
Sean Lange (9/26/2013)
Thanks to hunchback for the ddl and data. Here is another way to do the same thing.

select Rate
from @T
cross apply (select count(*) as RowsCount from @t where rate = @Rate) r
group by Rate
having COUNT(*) >= max(r.RowsCount)



Sean - Should this return Rate = 9? I think your approach simply returns any Rate that has 3 or more rows.

DECLARE @T TABLE (
Rate int NOT NULL,
Shift int NOT NULL,
PRIMARY KEY (Rate, Shift)
);

INSERT INTO @T (
Rate,
Shift
)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 5),
(3, 1),
(3, 2),
(3, 3),
(8, 4),
(4, 1),
(4, 2),
(5, 1),
(5, 2),
(5, 3),
(5, 4),
(6, 1),
(7, 2),
(8, 2),
(9, 2),
(9, 3),
(9, 4);


DECLARE @Rate int = 1;

select Rate
from @T
cross apply (select count(*) as RowsCount from @t where rate = @Rate) r
group by Rate
having COUNT(*) >= max(r.RowsCount)


I think this works as the OP requested also:

SELECT Rate
FROM @T
WHERE Shift IN (SELECT Shift FROM @T WHERE Rate = @Rate)
GROUP BY Rate
HAVING COUNT(*) = (SELECT COUNT(*) FROM @T WHERE Rate = @Rate);





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499140
Posted Thursday, September 26, 2013 9:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
I am a bit curious how hunchback and my solutions would scale against a divide and conquer approach.

SELECT Shift 
INTO #T
FROM @T
WHERE Rate = @Rate;
DECLARE @Rows INT = @@ROWCOUNT;

SELECT Rate
FROM @T
WHERE Shift IN (SELECT Shift FROM #T)
GROUP BY Rate
HAVING COUNT(*) = @Rows;

GO
DROP TABLE #T;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499157
Posted Thursday, September 26, 2013 10:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:47 AM
Points: 467, Visits: 615
well, almost all the sql masters have answered the questions.
a simple group by and having count will serve the purpose.
Post #1499167
Posted Thursday, September 26, 2013 11:05 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:25 AM
Points: 718, Visits: 547
Thanks hunchback, Sean Lange and dwain.c for help.

I'm going with CROSS APPLY as it has least logical reads and lesser cost then other two.

Thanks Again.
Post #1499176
Posted Thursday, September 26, 2013 11:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
If I am reading this article (http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx) correctly, this solution should also be pretty fast:

WITH Target AS
(
SELECT Rate, Shift
FROM @T
WHERE Rate = @Rate
)
SELECT c.Rate
FROM
(
SELECT Rate, rc=COUNT(*)
FROM @T
GROUP BY Rate
) a
JOIN
(
SELECT rc=COUNT(*)
FROM Target
) b ON b.rc <= a.rc
JOIN @T AS c ON c.Rate = a.Rate
JOIN Target AS d ON d.Shift = c.Shift
GROUP BY c.Rate
HAVING COUNT(*) = MIN(b.rc);





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499177
Posted Thursday, September 26, 2013 11:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
T.Ashish (9/26/2013)
Thanks hunchback, Sean Lange and dwain.c for help.

I'm going with CROSS APPLY as it has least logical reads and lesser cost then other two.

Thanks Again.


I do not recommend that you judge a query's performance by its plan cost.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499178
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse