SQL Query help
-
CREATE TABLE [dbo].[dp]([nr] [char](12) NOT NULL,
[type] [char](12) NOT NULL,
[DT] [int] NOT NULL,
)
INSERT INTO [dbo].[dp]
VALUES
('1','OF',49),
('2','OF',49),
('3','OF',49),
('4','CF',49),
('5','CF',49),
('6','CF',49),
('7','CF',49),
('1','AV',11),
('2','HK',11),
('3','HK',11),
('1','AV',67),
('2','HK',67),
('1','AV',18),
('2','SA',18),
('1','AV',80),
('2','AV',80)
CREATE TABLE [dbo].[ln](
[nr] [char](12) NOT NULL,
[type] [char](12) NOT NULL,
[DT] [int] NOT NULL,
)
INSERT INTO [dbo].[ln]
VALUES
('1','IL',70),
('2','IL',70),
('1','IL',69),
('2','IL',69),
('1','MI',89),
('2','MI',89),
('3','MI',89),
('4','MI',89)
I want a query that will only retrieve all DT that have only one type of nr
i.e, the outcome should be like below and the results should be union as the two table have two different functions with some minor difference in the structure
from the dp table the result expected should be'1','AV',80
'2','AV',80
from the ln table the result expected should be
'1','IL',70
'2','IL',70
'1','IL',69
'2','IL',69
'1','MI',89
'2','MI',89
'3','MI',89
'4','MI',89
Thank you
Carnalito
-
Lots of ways of doing this, here's one
WITH dpCTE AS (
SELECT [nr],
[type],
[DT],
RANK() OVER(PARTITION BY [DT] ORDER BY [type]) AS rn1,
RANK() OVER(PARTITION BY [DT] ORDER BY [type] DESC) AS rn2
FROM [dbo].[dp]),
lnCTE AS (
SELECT [nr],
[type],
[DT],
RANK() OVER(PARTITION BY [DT] ORDER BY [type]) AS rn1,
RANK() OVER(PARTITION BY [DT] ORDER BY [type] DESC) AS rn2
FROM [dbo].[ln])
SELECT [nr],
[type],
[DT]
FROM dpCTE
WHERE rn1=1 AND rn2=1
UNION ALL
SELECT [nr],
[type],
[DT]
FROM lnCTE
WHERE rn1=1 AND rn2=1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi
Thanks for the query, its returning what i want, but i have never used CTE and have no knowledge of it, guess its time to learn.
is there no easy way of doing it instead of the way you did it cause its so complicated.
How easy is it, say i want to join other tabes to ln and dp tables in order to display other information?
The reason is, i need extra information but its hosted in other different tables that i normally join to ln or dp.
Thank you in advance
-
shani19831 (8/6/2013)
Thanks for the query, its returning what i want, but i have never used CTE and have no knowledge of it, guess its time to learn.is there no easy way of doing it instead of the way you did it cause its so complicated.
How easy is it, say i want to join other tabes to ln and dp tables in order to display other information?
The reason is, i need extra information but its hosted in other different tables that i normally join to ln or dp.
Complicated? CTE's with Window (sometimes called Analytical) functions do the exact opposite. They un-complicate queries. It would be well worth your time to learn about ROW_NUMBER, RANK, NTILE, and a few others. Then look at examples on how to use them in CTE's. Once you understand their power, you will see opportunities to use them again and again. I keep a copy of "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" by Itzik Ben-Gan next to me on my desk. It is NOT on the shelf gathering dust.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
shani19831 (8/6/2013)
HiThanks for the query, its returning what i want, but i have never used CTE and have no knowledge of it, guess its time to learn.
is there no easy way of doing it instead of the way you did it cause its so complicated.
How easy is it, say i want to join other tabes to ln and dp tables in order to display other information?
The reason is, i need extra information but its hosted in other different tables that i normally join to ln or dp.
Thank you in advance
Unless you get into recursive CTE's just think of a CTE as a sub-query in the FROM clause (known as a derived table) except it's not in the FROM clause. It's still just a "derived table" or "inline view" just like a sub-query in a FROM clause.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Complicated? CTE's with Window (sometimes called Analytical) functions do the exact opposite. They un-complicate queries. It would be well worth your time to learn about ROW_NUMBER, RANK, NTILE, and a few others. "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" by Itzik Ben-Gan next to me on my desk.
I guess some of this functions are self taught as i have never come across them. Sometimes i wonder why in our university they only teach the basic SQL i.e INSERT,SELECT,UPDATE,DELETE and a few inner queries. I have studied the query you gave me and some how it makes sense, though it took me a while to get it. At the same time I'm modifying it to include what i want. The other thing, i have seen people using the HAVING function, i tried it but i got a few errors and will see if i can get it right. That book is worth having it and i will have to get it.
Unless you get into recursive CTE's just think of a CTE as a sub-query in the FROM clause (known as a derived table) except it's not in the FROM clause. It's still just a "derived table" or "inline view" just like a sub-query in a FROM clause.
After reading this comment i said to myself, wait then i looked at the CTE query above and it actually opened up my thoughts about inner queries and so forth.
One thing, on the CTE given above can i add a HAVING function at the END to say i only want to see those records where nr is greater than 2.
example
from the dp table'1','SAV',80
'2','SAV',80
from the ln table
'1','IL',70
'1','MIC',89
'2','MIC',89
'3','MIC',89
result should be only those that have more than one record, here the '1','IL',70 will be excluded from the result.
SELECT[nr],
[type],
[DT],
FROM lnCTE
WHERE acct_type1=1 AND acct_type2=1;
HAVING count(nr) >= 2
-
Hi Guys,
I have LEFT OUTER JOIN some tables in order to display the rest of the information, but the query takes 14:16 minutes to retrieve 42017 rows, which in my opinion is considerable too long.
Is there no other way i can write this in a simple manner that is fast?
-
Another way of doing this using GROUP BY,
with cte1 as (
select DT from dp group by DT having COUNT(distinct type)=1 and count(nr)>1
),
cte2 as (
select DT from ln group by DT having COUNT(distinct type)=1 and count(nr)>1
)
select dp.* from dp join cte1 on dp.dt=cte1.DT
UNION
select ln.* from ln join cte2 on ln.dt=cte2.DT
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply