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

SQL Query help Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 5:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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
Post #1481256
Posted Tuesday, August 6, 2013 6:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,678, Visits: 19,552
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1481285
Posted Tuesday, August 6, 2013 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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
Post #1481318
Posted Tuesday, August 6, 2013 10:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 355, Visits: 1,455
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/
Post #1481439
Posted Wednesday, August 7, 2013 12:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
shani19831 (8/6/2013)
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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481678
Posted Wednesday, August 7, 2013 1:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180

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

Post #1481698
Posted Wednesday, August 7, 2013 2:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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?
Post #1481733
Posted Wednesday, August 7, 2013 3:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 3:43 AM
Points: 28, Visits: 73
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

Post #1481743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse