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