How can I write this in single select query ?

Question

Post reply

How can I write this in single select query ?

prabhu.st

SSCrazy

Points: 2552
More actions
July 25, 2013 at 6:03 am

#395199

Hi I have a requirement which is little bit tedious as of now, can anyone please help me to solve this out.
Query
SELECT id,item FROM mytable ORDER BY somefield where id = 5
Output:
id Item
1 poor
2 ugly
3 evil
4 bad
5 God
Required Output:
id God
1 God
2 God
3 God
4 God
5 God
Though it is possible/simple in CTE, SubQuery,JOINs,Derived Tables,
but I want this to write in a single query...
Thanks in advance.,
Prabhu

Viewing 7 posts - 1 through 6 (of 6 total)

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

bleroy Hall of Fame Points: 3052 More actions · Answer 1

A little confused by your post ...

How can SELECT id,item FROM mytable ORDER BY somefield where id = 5

result in:

id Item

1 poor

2 ugly

3 evil

4 bad

5 God

???

did you misss out a "<"?

as for your single query, avoiding CTE, Joins, subqueries, or derived tables - I only see the following:

SELECT id, 'God' as [God] FROM mytable ORDER BY somefield

B

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

prabhu.st (7/25/2013)
... CTE, SubQuery,JOINs,Derived Tables,

All of these are elements of a single query. What do you really mean? If you mean "using a single query and without using any of these specific tools", then this must be an assignment of some kind. What have you tried?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

prabhu.st SSCrazy Points: 2552 More actions · Answer 3

Thanks Guys,

for reading my post,

finaly i got it,

yes,it is possible in the single query, it is..

select max(item) over()item from my_table where id = 5

Thanks,

Prabhu

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

prabhu.st (7/25/2013)
Thanks Guys,
for reading my post,
finaly i got it,
yes,it is possible in the single query, it is..
select max(item) over()item from my_table where id = 5
Thanks,
Prabhu

No, that doesn't work. It returns one row, one column. It is possible in a single query without using any of the tools you've listed. Let's see if you can figure it out first.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

prabhu.st SSCrazy Points: 2552 More actions · Answer 5

Yeah,

I agree with you, sorry, I was unable to reply you in time.

when I went on working out that, I found the issue,

then i wrote this one, which is working and also work for you.

declare @text varchar(100)

select @text = item from mytable where id = 5

select @text from mytable where id = 5

though it seems funny, for the god's sake , i am suppose to write it as per my requirement.

Thanks,

Prabhu

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Prabhu, I strongly recommend that you read and heed the article at the first link in my signature line below for posts like this. You'll get a lot better answers more quickly. I'll do it this time for you.

Here's one way to make the test data...

SELECT ID, Item

INTO #TestTable

FROM (

SELECT 1,'poor' UNION ALL

SELECT 2,'ugly' UNION ALL

SELECT 3,'evil' UNION ALL

SELECT 4,'bad' UNION ALL

SELECT 5,'God'

)d(ID,Item)

;

... and here's one solution... the logic is simple when you "Divide'n'Conquer" the two things that need to be done to find the solution.

SELECT t1.ID, t2.Item

FROM #TestTable t1

JOIN #TestTable t2

ON t1.ID <= t2.ID

AND t2.ID = 5

;

--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)