Concatenate field based on unique id. (Follow up)

thanks for your earlier reply.

If i want to order the IDs by a Timestamp column in descending order how do i do it. I couldn't do it in Inner query.
right now it gives in random order. Is there any other way to get it?

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, createTS, count(*) as seq
from your_table as t1 join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment, createTS
order by createTS desc -> gives error
) as t3
group by t3.id;

Got it working. I used 'top 100 percent" and used the ORDER BY in inner query.
Thanks.|||Note that using TOP 100 PERCENT is still not guaranteed to work. It depends on the query plan and even more so in SQL Server 2005. The use of ORDER BY clause is specific to a scope only and in your example to the derived table. Generally, you should not rely on the order in which the rows are processed in a SELECT statement. You should basically consider a SELECT statement source as an unordered set of rows. In your example, you can achieve the results by changing the condition:

t2.Comment <= t1.Comment

to

t2.CreateTs <= t1.CreateTs

This assumes that time stamp value is unique per id and this would guarantee that the sequence number is based on sorting the values in ascending order. You can incorporate additional conditions to handle matching time stamps and different comments. As I said before, doing these type of operations in SQL is not the right approach. These can be done very easily on the client side and with less work / assumptions.

Concatenate Columm Values from multiple Rows into a single col

Yes, the order is not guaranteed.
ML
http://milambda.blogspot.com/ML (ML@.discussions.microsoft.com) writes:
> Yes, the order is not guaranteed.
Not even that. You are not even guaranteed to get all rows. For 1, 2, 3, 4
you could get '1,2,3,4' or you could get only '4'.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That would make the function completely useless - could you give an example,
please? I've tested it with a few typical set-ups and have always found it t
o
return expected results.
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> That would make the function completely useless - could you give an
> example, please? I've tested it with a few typical set-ups and have
> always found it to return expected results.
Check out http://support.microsoft.com/default.aspx?scid=287515, and pay
particular attention to the first sentence under CAUSE.
Nevermind that the article then bend over backwards, to specify things that
may work. For me the conclusion is clear: don't rely on this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree using functions in the ORDER BY clause in this case is a disaster
waiting to happen, but since my function does not use them at all, were you
able to reproduce the problem anyway?
If you're too busy to play with this, I absolutely understand. I'm just
trying to learn new things every day. I promise I'll stop a few days after
I'm dead. ;)
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> I agree using functions in the ORDER BY clause in this case is a
> disaster waiting to happen, but since my function does not use them at
> all, were you able to reproduce the problem anyway?
My point is not that I can reproduce it here and now. My point is that
what works today, could break tomorrow.
For instance, there are people out there who have defined views in
SQL 2000 which goes:
SELECT TOP 100 PERCENT
..
ORDER BY
and they are happy because when they say:
SELECT * FROM view1
the see the data in order.
Then they move to SQL 2005 and get hit, because the optimizer is now
less likely to return the data in order. The truth was all the time
that without an ORDER BY, the order of the data is undefined.
See also
http://lab.msdn.microsoft.com/produ...b9-3dd863ae6b1c
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This bug report clears up the matter greatly. Thank you very much. I intend
to include this example in my blog as a warning ASAP.
I've searched the web for this issue, but found no usable references. Thanks
again.
ML
http://milambda.blogspot.com/

Concat

Hi
I want to Concat Rows of a Column in a Table with One "Select" Order (I mean without use cursor)

Example:

Table1:
Column1
----
'a'
'b'
'c'

Result:
'abc'

Please answer me

thanks alotif you're using MySQL, use the GROUP_CONCAT function

if you're using Sybase ASE, use the LIST function

otherwise, a cursor is actually not a bad idea, because other database systems don't have a similar aggregate functionsqlsql

Compound Primary Key - order not as expected

Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?John Rivers wrote:
> Hello,
> if you create this table:
> create table hello (
> int a
> , int b
> constraint pk_hello primary key clustered ( a, b )
> )
> and then insert the following records
> a,b
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> and then do
> select a,b from hello
> the output seems to be:
> a,b
> 1,1
> 2,1
> 3,1
> 1,2
> 2,2
> 3,2
> 1,3
> 2,3
> 3,3
> which is wrong and (i think) is reflecting the actual index order
> and physical order on disk

This is not wrong at all. As long as you do not have an "ORDER BY"
clause the RDBMS is free to return records in *any* order.

> it should be:
> a,b
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> i have tested this on a table with 500,000 records
> and sure enough if you declare the clustered primary key fields in
> reverse order:
> constraint pk_hello primary key clustered ( b, a )
> two things happen:
> - the select with no order by returns the records in the expected order

Again: you have to adjust your expectations.

> - queries relying on that order run MUCH FASTER
> has anyone else seen / noticed this?

Yes.

Cheers

robert|||Order is not guaranteed unless you include an ORDER BY. This is by design.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John Rivers" <first10@.btinternet.com> wrote in message
news:1146048739.469710.138210@.e56g2000cwe.googlegr oups.com...
Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You need to read a book on RDBMS; you are still locked into a file
system mind set.|||Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

this can easily be proved by watching the Execution Plan

the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected

maybe you can enjoy reading that RDBMS book :-)

best wishes,

john|||John Rivers wrote:
> Hello,
> when a clustered index is present the records *are* physically ordered
> on disk to match the index
> that is the whole point of a clustered index
> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)
Um. No. I've seen it return them out of order with only a few hundred
rows. As soon as the table is occupying more than one page, the query
optimizer *can* decide to produce a parallel plan. You'll see the
result as chunks of output which are in clustered index order, but no
deterministic ordering between the chunks. e.g. it'll look like:

1
2
3
4
5
11
12
13
14
15
6
7
8
9
10

The *only* way to guarantee the order of output is to put an order by
clause on your select statement.

Damien|||John Rivers wrote:
> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)

Not true at all. As Joe says, tables are not logically ordered. There
is no guarantee that any queries will match the physical order on disk
or in a clustered index.

--
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/...US,SQL.90).aspx
--|||On 28 Apr 2006 05:41:26 -0700, John Rivers wrote:

>Hello,
>when a clustered index is present the records *are* physically ordered
>on disk to match the index
>that is the whole point of a clustered index

Hi John,

Correct.

>and by default a select statement with no ORDER BY will always return
>data in the order of the clustered index (when present)

Incorrect. Damien already pointed out the risk of parallellism.

Another potential issue is an optimization technique MS employs called
"piggybacking" - if a query on another connection is in the middle of a
tbale scan on the table you need, the DB will use the values coming in
for your query as well, then (when the first query's table scan is
finished) restart the scan from start up to where it started to
piggyback. The results would be like 6 - 7 - 8 - 9 - 10 - 1 - 2 - 3 - 4
- 5

This is almost impossible to reproduce in a test environment, but it
MIGHT happen intermittently in a heavily used production DB. Tough lluck
if your app expects the rows to be in order, even without ORDER BY.

>the issue i am trying to highlight concerns the order of the records on
>disk when a *compound* clustered index is present
>i have seen cases when it is not as expected

How did you "see" those cases? Using a query reallly doesn't prove
anything. Did you issue DBCC PAGE commands to inspect the actual
contents of the index and data pages?

--
Hugo Kornelis, SQL Server MVP|||John Rivers (first10@.btinternet.com) writes:
> when a clustered index is present the records *are* physically ordered
> on disk to match the index
> that is the whole point of a clustered index

Actually, they are ordered if you follow the page links. But if pages
are in disorder, the physical order on disk may be yet another one.

> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)

No. This may have been true by chance for SQL Server up version 6.5. It is
definitely not correct for SQL 7 and later.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your knowledgable answers

I will check out DBCC PAGE

composite index and column order

Hi,

I created a composite index (lastname, firstname). I know the following
queries will use this index:

WHERE lastname = ...
WHERE lastname = ... AND firstname = ...

Also this won't use the index:
WHERE firstname = ...

But how about: WHERE firstname = .. AND lastname = ...

And why?

Thanks a lot,

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGBaihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...
> But how about: WHERE firstname = .. AND lastname = ...

It will use the index.

> And why?

Because, as far as the query optimizer is concerned, these two are
exactly the same:

WHERE lastname = ... AND firstname = ...
WHERE firstname = ... AND lastname = ...|||Thanks for your help, I really appreciate it.

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG|||Baihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...

Not necessarily. Consider the following query:

select lastname, firstname from some_table where firstname = ...

It will use the index, and, more to the point, it will not touch the
table at all - the index already has all the information the query
needs. It is called "index covering".|||Alexander Kuznetsov wrote:
> Baihao Yuan wrote:
> > Hi,
> > I created a composite index (lastname, firstname). I know the following
> > queries will use this index:
> > WHERE lastname = ...
> > WHERE lastname = ... AND firstname = ...
> > Also this won't use the index:
> > WHERE firstname = ...
> Not necessarily. Consider the following query:
> select lastname, firstname from some_table where firstname = ...
> It will use the index, and, more to the point, it will not touch the
> table at all - the index already has all the information the query
> needs. It is called "index covering".

No, it won't. If you had a list of people on a piece of paper, sorted
by last name and then by first name, explain how you would use that
list to find everyone with a first name of "Joe" without looking
through the entire list.|||I created a table called tblNames with nonClustered index defined on
lastname,firstname (composite index).

select * from tblnames where lastname = 'smith'--Performed an Index
Seek

select * from tblnames where lastname = 'smith' and firstname =
'john'--Performed an Index Seek

select * from tblnames where firstname = 'john'--Performed a Table Scan

select * from tblnames where firstname = 'john' and lastname =
'smith'--Performed an Index Seek

ZeldorBlat wrote:
> Alexander Kuznetsov wrote:
> > Baihao Yuan wrote:
> > > Hi,
> > > > I created a composite index (lastname, firstname). I know the following
> > > queries will use this index:
> > > > WHERE lastname = ...
> > > WHERE lastname = ... AND firstname = ...
> > > > Also this won't use the index:
> > > WHERE firstname = ...
> > > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.|||> > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Why don't you try it out in practice? You might be in for some
surprise. If the index is smaller than the table, and contains all the
necessary information, it is likely to be used instead of the table.
Google up "index covering".|||Alexander Kuznetsov wrote:
> > > Not necessarily. Consider the following query:
> > > > select lastname, firstname from some_table where firstname = ...
> > > > It will use the index, and, more to the point, it will not touch the
> > > table at all - the index already has all the information the query
> > > needs. It is called "index covering".
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Why don't you try it out in practice? You might be in for some
> surprise. If the index is smaller than the table, and contains all the
> necessary information, it is likely to be used instead of the table.
> Google up "index covering".

I did try it in practice -- as did the OP who posted his results in
this thread.|||>
> select * from tblnames where firstname = 'john'--Performed a Table Scan

If you only select 2 columns, firstname, lastname

select columns, firstname from tblnames where firstname = 'john'

and the table has a lot of other columns, the index is likely to be
used even if firstname is not the first column in the index. The reason
is simple: the index contains all the information necessary to satisfy
the query and it is smaller than the table.|||> I did try it in practice -- as did the OP who posted his results in
> this thread.

the OP tried for

select * from ...

while I was speaking aobut

select lastName, firstname from ...

Big difference.|||correction:

If you only select 2 columns, firstname, lastname

select firstname, lastname from tblnames where firstname = 'john'

> and the table has a lot of other columns, the index is likely to be
> used even if firstname is not the first column in the index. The reason
> is simple: the index contains all the information necessary to satisfy
> the query and it is smaller than the table.|||ZeldorBlat (zeldorblat@.gmail.com) writes:
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Say further that with each list there is a page number to references
where the persons appear in the book.

If all you want to know is the name of the persons, you can scan
the index, you don't have to read the whole book.

It's important to keep in mind that an index can be used in two
ways: Seek (look up data through the index tree) and Scan (read
the entire index from left to right): While the latter is far more
expensive, it can still be useful at times.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> ZeldorBlat (zeldorblat@.gmail.com) writes:
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Say further that with each list there is a page number to references
> where the persons appear in the book.
> If all you want to know is the name of the persons, you can scan
> the index, you don't have to read the whole book.
> It's important to keep in mind that an index can be used in two
> ways: Seek (look up data through the index tree) and Scan (read
> the entire index from left to right): While the latter is far more
> expensive, it can still be useful at times.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Point taken. Thanks, Erland.|||I'd like to repeat the suggestion to do your own experimenting. The
technology evolves quite fast, so anything you might have read in any
book / article / whatever esle may be already obsolete. The optimizer
is way smarter now than it used to be 5 or 10 years ago. In this
particular case you did not need to read anything, you could just take
any table of, say, 100K rows, with, say, 20 columns, create an index on
it

create index i1 on t1(col1, col2)

and see the execution plan for the query

select col1, col2 from t1 where col2 =...

That's all it takes, it's that simple.

Good luck!

Composite clustered index - column order

Want to check my thinking with you folks...

I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.

Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000

C3 is effectively number of seconds since 01/01/1970.

The usage of the table is typically, insert a row, do something else, then update it.

Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.

My thinking is that this composite index is better ordered C1,C2,C3.

My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.

Welcome your thoughts.

What are the data types of these columns? If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits. That usually happens with varchars that are updated to a larger value for example. You are usually better off to have a narrow clustered index.

What are you trying to accomplish here? Are you worried about SELECT performance, INSERT/UPDATE performance, or about index size and maintenance?

If C3 is being updated a lot, you might be better off to have the clustered index on C1, C2, and then have a non-clustered index on C3.

|||

"What are the data types of these columns"

char(4),Char(4) and int

"If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits"

Together the 3 columns provide unique key, and none of the columns are updated. The page splitting aspect I'm considering is, if the first column in the clustered is effectively an identity (so the next value inserted can only ever be bigger than the last), does this bias the inserts to one side of the tree - page splits being necessary there, because a fill factor spreads the free space throughout the tree?

"You are usually better off to have a narrow clustered index"

Yes. I appreciate that, because it gets tagged onto all non-clustered indexes. Let's assume that space isn't an issue.

Looking for best pewrformance for select \ insert & update. Index size & maint not an issue.

Thanks

COMPLEX sql query PLEASE HELP!

I cant get "order by" to work in this sql query..
I use this query:

"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"
and I want to add this some where to get 12 random records: "ORDER BY NewID()"

I tried this:"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()""
but get the error:
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"

I can′t figure out how I should write the query..
Somebody have any ideas??
/Radiwoi

Try something like this:
SELECT TOP 12 name, total FROM (SELECT name, total = COUNT(*) FROM products WHERE kat = 'music' GROUP BY name) AS groupedSet ORDER BY NEWID()

The subquery generates a set in the output format that you want, the main query just orders it randomly and restricts it to 12 rows.

Does this help?
|||Thanks for your answer and thanks for your help It worked perfect.
you are KING!!!

Complex Order By Logic

I apologize if this is not the appropriate forum for this question.

I've

written a searchable database-driven application in classic ASP and

vbscript with a SQL Server backend. What I need to do is this: order

the results of a query so that if the first "order by" field is null to

order that entry based on the second "order by" field.

The

application I am writing is a database of books, and my client wants

the results to be ordered by Author, unless there is no Author, in

which case he wants that entry ordered by book title. Here is an

example of how he wants the books sorted:

Adamson, Jan - Book X
Bible, The
Wilson, Jonathan - Book Y

I

hope I've explained the scenario correctly. What's the best way to

write a SQL statement that will yield the ordering criteria described

above? Also, is there any way to get "Order by" to ignore articles like

"A" and "The?"

I personally hate this idea, as it would be annoying to scan through. I would put no authors at the end or beginning as 'No Author.'

You can use coalesce to do this:

create table authorTitle
(
author varchar(20),
title varchar(20)
)
insert into authorTitle
select 'Adamson, Jan','Book X'
union
select NULL, 'Bible, The'
union
select 'Wilson, Jonathan','Book Y'
go

select *
from authorTitle
order by coalesce(author,'') + coalesce(title,'')

author title
-- --
Adamson, Jan Book X
NULL Bible, The
Wilson, Jonathan Book Y

You will probably going to want to add this as a computed column, and likely index it to get this to get this to perform well if you have lots of books in the database.

|||Thank you! That did the trick.

Complex order by clauses

I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)

I thought I'd be able to do something like:

ORDER BY (price != 0), price

thinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.

any suggestions?

thanks!

try to do SOMETHING LIKE IN this EXAMPLE:

select

*into #testfrom(select 129.89 price)aa

insert

into #testselect 19.89

insert

into #testselect 1.89

insert

into #testselect 49.89

insert

into #testselect 39.89

insert

into #testselect 29.89

insert

into #testselect 0

insert

into #testselect 0

select

*from #TEst

order

by
case price
When 0then 1
else
0
END,
price

drop

table #test

Complex ORDER BY - possible?

I want to sort recrords by two columns, but would like to order them by a fixed value in the first column.

Example.

I have an employee database, and want to sort by SKILL, YEARS_EXPERIENCE.

But I want a specific skill listed first, then all other skills.

Such as (I just made this up):

SELECT * from emp_master order by (SKILL='C#', YEARS_EXPERIENCE DESC), (SKILL <> 'C#', YEARS_EXPERIENCE DESC).

So my results would be:

C#, 10
C#, 7
C#, 5
ASP.NET, 10
ASP.NET, 9
ASP.NET, 5
SQL, 5
SQL, 4
VB, 5
VB, 3

This is handy for 'near' matches where I want a preferred result to filter to the top, but all results in some order.

Is this possible?One thing you might consider is returning the results for C# and sorting those, then UNION joining that to a result set that does NOT contain C#.|||UNION still just sorts by the common sort criteria, unless I am doing something wrong.

If I use two SQL statements, SQL A chooses 'C#' and years DESC, the SQL B chooses <> 'C#' and years DESC, and then UNION, they all come back in order of the years DESC without the C# being first on the list.|||You should be able to use a CASE statement:

SELECT * from emp_master
ORDER BY
CASE [Skill] WHEN 'C#' THEN 0 ELSE 1 END,
Skill,Years_Experience DESC

|||Yup, that'll definitely do it! :)|||EOM