Multiple rows

Hi everybody:
I have this result from a query:
category id pos name
Fixed
1 100009 999003 BLA BLA BLA 1
1 100010 999003 BLA BLA BLA 1
2 100321 999007 EX EX EX 0
2 100322 999007 EX EX EX 0
2 100323 999007 EX EX EX 0
2 100324 999007 EX EX EX 0
2 100325 999007 EX EX EX 0
2 100326 999007 EX EX EX 0
Is there any way (not using UNION) to get the rows where Fixed NOT 0
but only one from where Fixed = 0 ?<acidscan@.gmail.com> wrote in message
news:1175639463.958180.180500@.n76g2000hsh.googlegroups.com...
> Hi everybody:
> I have this result from a query:
> category id pos name
> Fixed
> 1 100009 999003 BLA BLA BLA 1
> 1 100010 999003 BLA BLA BLA 1
> 2 100321 999007 EX EX EX 0
> 2 100322 999007 EX EX EX 0
> 2 100323 999007 EX EX EX 0
> 2 100324 999007 EX EX EX 0
> 2 100325 999007 EX EX EX 0
> 2 100326 999007 EX EX EX 0
> Is there any way (not using UNION) to get the rows where Fixed NOT 0
> but only one from where Fixed = 0 ?
>
Please post DDL so that we don't have to guess what the keys are. Please
post the query that produces this result.
Explain the logic. Which row do you want to return where Fixed = 0? You mean
just some random row?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||id pos name Fixed
100009 999003 BLA BLA BLA 1
100010 999003 BLA BLA BLA 1
100321 999007 EX EX EX 0
100322 999007 EX EX EX 0
100323 999007 EX EX EX 0
100324 999007 EX EX EX 0
100325 999007 EX EX EX 0
100326 999007 EX EX EX 0
I made it shorted to remove the format. This is a simple select
returning some elements from a table. What i would like to do is only
show the first 3 elements based on the "fixed" column, first showing
the elements where "fixed" is not 0 and later all the elements where
"fixed" is 0 grouped into one row.
I could do this making a select where fixed != 0 and a "union all"
with another select where fixed = 0 using only the first element. This
union will return:
id pos name Fixed
100009 999003 BLA BLA BLA 1
100010 999003 BLA BLA BLA 1
100321 999007 EX EX EX 0
My question is: is there any shorter way (not using the union) to do
this ?|||<acidscan@.gmail.com> wrote in message
news:1175642227.533516.273640@.y66g2000hsf.googlegroups.com...
> id pos name Fixed
> 100009 999003 BLA BLA BLA 1
> 100010 999003 BLA BLA BLA 1
> 100321 999007 EX EX EX 0
> 100322 999007 EX EX EX 0
> 100323 999007 EX EX EX 0
> 100324 999007 EX EX EX 0
> 100325 999007 EX EX EX 0
> 100326 999007 EX EX EX 0
> I made it shorted to remove the format. This is a simple select
> returning some elements from a table. What i would like to do is only
> show the first 3 elements based on the "fixed" column, first showing
> the elements where "fixed" is not 0 and later all the elements where
> "fixed" is 0 grouped into one row.
> I could do this making a select where fixed != 0 and a "union all"
> with another select where fixed = 0 using only the first element. This
> union will return:
> id pos name Fixed
> 100009 999003 BLA BLA BLA 1
> 100010 999003 BLA BLA BLA 1
> 100321 999007 EX EX EX 0
> My question is: is there any shorter way (not using the union) to do
> this ?
>
You didn't specify a key so I'll assume that ID is the key. If I'm right,
try:
SELECT id, pos, name, fixed
FROM tbl
WHERE fixed <> 0
OR id =
(SELECT MIN(id)
FROM tbl
WHERE fixed = 0);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--