Query tweak required

Question

Query tweak required

Bhuvnesh

SSC Guru

Points: 59351
More actions
September 17, 2010 at 1:30 am

#238406

Select top 5 'MY DATABASE TABLE: ' + name from sysobjects where xtype = 'u'
order by 1 desc
Present output:
MY DATABASE TABLE: WEBEX_MEETING_PRODUCT_ATTENDEE
MY DATABASE TABLE: WEBEX_ACCOUNT_CONFIG
MY DATABASE TABLE: VENDOR
MY DATABASE TABLE: USER_REQUEST
MY DATABASE TABLE: USER_GROUP
required output:
MY DATABASE TABLE :
LU_SFORCE_MULTIPLE_MATCH_ACTION_TYPE
LU_LAST_OPT_OUT_BY
LU_CHART_TYPE
LU_AGENDA_ITEM_CATEGORY
ACCOUNT_ROOMMATE_EMAIL
but i DONT want to use UNION here like select 'MY DATABASE TABLE : ' union
Select top 5 name from sysobjects where xtype = 'u'
order by 1 desc
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 8 (of 8 total)

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

hallidayd SSCrazy Points: 2490 More actions · Answer 1

How come you don't want to use UNION? It is possible to write a query that does this as a set without UNION but it is very contorted.

Is a variable ok?

DECLARE@string AS NVARCHAR(MAX)

SELECT TOP 5

@string = COALESCE(@string, N'MY DATABASE TABLE: ') + CHAR(10) + name

FROM sys.tables

ORDER BY

name DESC

SELECT @string

hallidayd SSCrazy Points: 2490 More actions · Answer 2

And here it is. Nasty, eh?

SELECT TOP 6

the_output = CASE

WHEN tables.name = yucky.first_table THEN

N'MY DATABASE TABLE: '

ELSE

name

END

FROM sys.tables

CROSS JOIN

(

SELECT first_table = MIN(name)

FROM sys.tables

) AS yucky

ORDER BY

CASE

WHEN tables.name = yucky.first_table THEN

CHAR(255)

ELSE

name

END DESC

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

How about an outer join from a tally table (where row = required number of rows+1) to a cte which has a rownumber function.

The you simply need to isnull the non-joined tally table row.

Clear Sky SQL
My Blog[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 4

Probably not what you're looking for, but how's this?

Select top 5 'MY DATABASE TABLE: ' = name

from sysobjects

where xtype = 'u'

order by 1 desc

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

WayneS (9/17/2010)
Probably not what you're looking for, but how's this?
Select top 5 'MY DATABASE TABLE: ' = name
from sysobjects
where xtype = 'u'
order by 1 desc

Thanks wayne but i posted it in my first post 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Bhuvnesh (9/19/2010)
WayneS (9/17/2010)
Probably not what you're looking for, but how's this?
Select top 5 'MY DATABASE TABLE: ' = name
from sysobjects
where xtype = 'u'
order by 1 desc
Thanks wayne but i posted it in my first post 🙂

Ummm.... Nope... you didn't. Look again... a bit more carefully this time. 😉

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

Jeff Moden (9/19/2010)
Bhuvnesh (9/19/2010)
WayneS (9/17/2010)
Probably not what you're looking for, but how's this?
Select top 5 'MY DATABASE TABLE: ' = name
from sysobjects
where xtype = 'u'
order by 1 desc
Thanks wayne but i posted it in my first post 🙂
Ummm.... Nope... you didn't. Look again... a bit more carefully this time. 😉

oohhh .PERFECT solution from wayne ( 90$ to wayne and 10$ for jeff)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)