Urgent select question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Urgent select question

Author

Topic

dani2
Starting Member

32 Posts

Posted - 2006-10-30 : 01:53:34

How can I select more rows than maximum rows in a table. There is no secondary table to join to.

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 01:56:47

You can make a CROSS JOIN and alias the single table again, as

SELECT      *
FROM        MyTable a
CROSS JOIN  MyTable b

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 02:01:02

"How can I select more rows than maximum rows in a table"
What are you trying to achieve ?

"There is no secondary table to join to."
Join with a derived number table

SELECT      *
FROM        MyTable a 
            cross join 
            (
                select num = 0 union all 
                select num = 1 union all
                select num = 2
            ) n

dani2
Starting Member

32 Posts

Posted - 2006-10-30 : 02:07:56

quote:

SELECT      *
FROM        MyTable a 
            cross join 
            (
                select num = 0 union all 
                select num = 1 union all
                select num = 2
            ) n

I have a table with a variable number of rows. I need a query that returns maximum number of rows everytime.
Your query is perfect except I want empty rows added.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 02:11:16

Use UNION

SELECT    Col1, Col2
FROM      MyTable
UNION ALL
SELECT    NULL, NULL

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 02:11:47

I think you have to explain what ou are trying to acheive.

Peter Larsson
Helsingborg, Sweden

dani2
Starting Member

32 Posts

Posted - 2006-10-30 : 02:18:44

quote:
Originally posted by Peso

I think you have to explain what ou are trying to acheive.

Peter Larsson
Helsingborg, Sweden

Ok. Lets say there is a table with 2 cols
[Name] varchar, [Value] Decimal
The table may have 1 to 5 rows. I need a query that returns 5 rows no matter how many rows are in the table.
For example
test 100
test2 200
null null
null null
null null
if the table has only 2 rows

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 02:20:49

[code]
select top 5 *
from
(
select [Name], [Value]
from [Table]

union all

select NULL, NULL
from (
select 1 as num union all
select 2 as num union all
select 3 as num union all
select 4 as num union all
select 5
) n
) a
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 02:21:30

But why do you need the constant number of rows to return ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 02:25:32

i don't think you need two levels of derived tables.

select top 5	x.*
from		(
			select	num, name
			from	[Tab2]
			union all
			select	NULL, NULL
			union all
			select	NULL, NULL
			union all
			select	NULL, NULL
			union all
			select	NULL, NULL
			union all
			select	NULL, NULL
		) x

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 02:26:03

I think it has to do with client side report generator.

Peter Larsson
Helsingborg, Sweden

dani2
Starting Member

32 Posts

Posted - 2006-10-30 : 02:27:32

quote:
Originally posted by khtan

But why do you need the constant number of rows to return ?

KH

I have to make a report in a very clear specified format. better dont ask :)
thanks a lot, its exactly what i want

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 02:28:43

>> i don't think you need two levels of derived tables.
You are correct.

Was originally intended to make a number table out of cross join and specify the max records in where condition. But since it is only 5 reocrds, decided to simply it a bit.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 02:31:32

quote:
Originally posted by dani2

quote:
Originally posted by khtan

But why do you need the constant number of rows to return ?

KH

I have to make a report in a very clear specified format. better dont ask :)
thanks a lot, its exactly what i want