Column for Table1 within subquery

Question

Column for Table1 within subquery

DEEPAK GUPTA-378433

Valued Member

Points: 53
More actions
July 29, 2016 at 8:37 am

#328579

Hello All
How could I use Column for Table1 within subquery. I know this is not possible but there should be a nice way to achieve this.
SELECT *
FROM Table1 K1
WHERE (1) = (
SELECT COUNT(DISTINCT(K2.KiaId))
FROM Table1 K2
WHERE K2.KiaId >= K1.KiaId and K2.KiaKIID = K1.KiaKIID)
Thanks in Advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

DEEPAK GUPTA-378433 (7/29/2016)
Hello All
How could I use Column for Table1 within subquery. I know this is not possible but there should be a nice way to achieve this.
SELECT *
FROM Table1 K1
WHERE (1) = (
SELECT COUNT(DISTINCT(K2.KiaId))
FROM Table1 K2
WHERE K2.KiaId >= K1.KiaId and K2.KiaKIID = K1.KiaKIID)
Thanks in Advance

Your question makes no sense. What are you trying to do here? Please see the first article in my signature for best practices in making this question better.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

DEEPAK GUPTA-378433 (7/29/2016)
Hello All
How could I use Column for Table1 within subquery. I know this is not possible but there should be a nice way to achieve this.
SELECT *
FROM Table1 K1
WHERE (1) = (
SELECT COUNT(DISTINCT(K2.KiaId))
FROM Table1 K2
WHERE K2.KiaId >= K1.KiaId and K2.KiaKIID = K1.KiaKIID)
Thanks in Advance

Are you referring to something like this?

SELECT *

FROM Table1 K1

CROSS APPLY (

SELECT COUNT(DISTINCT(K2.KiaId)) CntDisKiaId

FROM Table1 K2

WHERE K2.KiaId >= K1.KiaId and K2.KiaKIID = K1.KiaKIID

HAVING COUNT(DISTINCT(K2.KiaId)) = 1) K2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2