SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query help


SQL Query help

Author
Message
shani19831
shani19831
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 231
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
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6712 Visits: 25623
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




shani19831
shani19831
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 231
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
LinksUp
LinksUp
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1771 Visits: 4640
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/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222292 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
shani19831
shani19831
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 231

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


shani19831
shani19831
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 231
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?
MD21
MD21
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 149
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search