possible to select top 5 * of 2 or more individual criteria?

Hello,
I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
but in the same output I also want to include
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
I have thousands of rows where fldx starts with 'T' and 'S'. Is it possible
to select the desired rows above in the same output? what is the tsql for
this?
Thanks,
RichTry
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
UNION ALL
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
Keep in mind that the top clause doesn't have much meaning without an ORDER
BY, unless you are relying on the automatic ordering done on a table's
primary key.
"Rich" wrote:

> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it possib
le
> to select the desired rows above in the same output? what is the tsql fo
r
> this?
> Thanks,
> Rich
>|||use union all.
btw, what does "top 5" mean without an "order by" clause? also, "where fldx
like 'T%'" would much likely produce a more efficient exec plan than the
substring function on the column.
dean
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0D2A3956-4E96-4FDE-BA84-116F27C25856@.microsoft.com...
> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it
> possible
> to select the desired rows above in the same output? what is the tsql
> for
> this?
> Thanks,
> Rich
>|||SELECT * FROM
(SELECT TOP 5 * -- always use COLUMN LIST!
FROM tbl1
WHERE LEFT(fldx,1) = 'T'
ORDER BY '
) T
UNION ALL
(SELECT TOP 5 * -- always use COLUMN LIST!
FROM tbl1
WHERE LEFT(fldx,1) = 'S'
ORDER BY '
) S
ORDER BY '
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0D2A3956-4E96-4FDE-BA84-116F27C25856@.microsoft.com...
> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it
> possible
> to select the desired rows above in the same output? what is the tsql
> for
> this?
> Thanks,
> Rich
>|||Thank you all for your replies. I was working with substring earlier on
picking out 3 letters from a word, so that stuck in my brain. And I forgot
about including Order By for Top clause, and I was not even thinking about
Union All.
Thanks all for your help.
Rich
"Rich" wrote:

> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it possib
le
> to select the desired rows above in the same output? what is the tsql fo
r
> this?
> Thanks,
> Rich
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ez$bhXSIGHA.1180@.TK2MSFTNGP09.phx.gbl...
> SELECT * FROM
> (SELECT TOP 5 * -- always use COLUMN LIST!
> FROM tbl1
> WHERE LEFT(fldx,1) = 'T'
> ORDER BY '
> ) T
> UNION ALL
> (SELECT TOP 5 * -- always use COLUMN LIST!
> FROM tbl1
> WHERE LEFT(fldx,1) = 'S'
> ORDER BY '
> ) S
> ORDER BY '
Have you done a search for SELECT * in 2005 bol?
:)|||Do 800 hits make it a good practice? I don't think so.
ML
http://milambda.blogspot.com/|||> Have you done a search for SELECT * in 2005 bol?
Microsoft does plenty of things that violate best practices. Doesn't mean
you should do it too, and it certainly doesn't mean that I should advocate
it either.|||> Have you done a search for SELECT * in 2005 bol?
Microsoft does plenty of things that violate best practices. Doesn't mean
you should do it too, and it certainly doesn't mean that I should advocate
it either.|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B77D3202-71FC-412D-A0BB-E8EEE669A599@.microsoft.com...
> Do 800 hits make it a good practice? I don't think so.
Perhaps the next time you advocate someone reading BOL
to get an intro to sql server you should use a asterick :)

possible to concatenate text to Where clause?

declare @.s varchar(300)
set @.s = ' recID = 100'
Select * from tbl1 Where + @.s
This returns a syntax error. Is it possible to concatenate text like this
to the Where clause? What does the correct syntax look like?
Thanks,
Richbetter yet - is it possible to use If/Else or select case in a where clause?
"Rich" wrote:

> declare @.s varchar(300)
> set @.s = ' recID = 100'
> Select * from tbl1 Where + @.s
> This returns a syntax error. Is it possible to concatenate text like this
> to the Where clause? What does the correct syntax look like?
> Thanks,
> Rich|||I seriously doubt this will help, but here is a small sample.
Any/every time I've tried to put a case statement in the where clause, I
fall flat.
But here is a small sample anyways.
You might want to check my article at:
http://www.sqlservercentral.com/col...lem.as
p
also.
declare @.price float
select @.price = 0.00
select @.price = 2.9900
--select @.price = null
select top 5 * , title, price
from pubs.dbo.titles
where
case
when @.price = 0 then price --here i am saying.. i didn't actually supply a
price.. so match the price with the price (aka, it gets everything because
price will always match price )
when @.price is null then price
else @.price -- aka, since i actually supplied a @.price, then match
the @.price with the price
end
= price -- the db field
----
---
-- the CASE statement above will translated into 1 of the two items below
select top 5 * , title, price
from pubs.dbo.titles where price = price -- aka, will always match, so you
get everything
select top 5 * , title, price
from pubs.dbo.titles where @.price = price --aka, only if the db value for
price matches the variable value for @.price
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> better yet - is it possible to use If/Else or select case in a where
clause?
> "Rich" wrote:
>
this|||Thanks for your reply. This does help actually. What I was trying to do
was to use an SP as a recordsource for a form, but I changed my mind and
decided to go with an inline function. This works, and I can still use the
case statement inside the function.
May I ask, how do you make arguments optional for a function?
"sloan" wrote:

> I seriously doubt this will help, but here is a small sample.
> Any/every time I've tried to put a case statement in the where clause, I
> fall flat.
> But here is a small sample anyways.
> You might want to check my article at:
> http://www.sqlservercentral.com/col...lem.
asp
> also.
>
>
> declare @.price float
> select @.price = 0.00
> select @.price = 2.9900
> --select @.price = null
> select top 5 * , title, price
> from pubs.dbo.titles
>
> where
> case
> when @.price = 0 then price --here i am saying.. i didn't actually supply
a
> price.. so match the price with the price (aka, it gets everything because
> price will always match price )
> when @.price is null then price
> else @.price -- aka, since i actually supplied a @.price, then match
> the @.price with the price
> end
> = price -- the db field
> ----
--
> ---
> -- the CASE statement above will translated into 1 of the two items below
> select top 5 * , title, price
> from pubs.dbo.titles where price = price -- aka, will always match, so y
ou
> get everything
> select top 5 * , title, price
> from pubs.dbo.titles where @.price = price --aka, only if the db value fo
r
> price matches the variable value for @.price
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> clause?
> this
>
>|||Rich
Unless I am completely wrong, I feel its fairly straightforward. try this.
declare @.s varchar(300)
set @.s = ' recID = 100'
exec('Select * from tbl1 Where ' + @.s)|||You can do this, but it's a horrible idea if @.s is
user-input or based on user-supplied data or metadata.
If @.s is something like '1=1; drop table tbl1' and the
code is executed with sufficient privilege, boom! Bye,
bye table 1. Worse and less noticeable things can
happen, too.
Read
http://www.*sommarskog*.se/dynamic_sql.html
http://www.unixwiz.net/techtips/sql-injection.html
Steve Kass
Drew University
Omnibuzz wrote:

>Rich
> Unless I am completely wrong, I feel its fairly straightforward. try this
.
>declare @.s varchar(300)
>set @.s = ' recID = 100'
>exec('Select * from tbl1 Where ' + @.s)
>
>