ORDER BY Question

Item Quantity
I1 10
I2 7
I1 5
Assume that is the Data.
I want it ordered by Quantity FIRST and then show the same Item again
if it exists.
How can I display it in this way
Item Quantity
I1 10
I1 5
I2 7
P.S Please do not say "ORDER BY Item, Quanity DESC"I'll guess you want to order by max(Quantity) for the same item.
select
T.Item,
T.Quantity
from T
join (
select Item, max(Quantity) as maxQuantity
from T
group by Item
) as Tmax
on Tmax.Item = T.Item
order by maxQuantity desc
[Not tested, but I hope you get the idea]
Steve Kass
Drew University
rythm wrote:

> Item Quantity
>I1 10
>I2 7
>I1 5
>Assume that is the Data.
>I want it ordered by Quantity FIRST and then show the same Item again
>if it exists.
>How can I display it in this way
>Item Quantity
>I1 10
>I1 5
>I2 7
>P.S Please do not say "ORDER BY Item, Quanity DESC"
>|||Thanks that did work.
After looking at it, it seems so simple.
I couldn't even think of performing it in that ordersql

Order by of a table passed as parameters?

Is it possible to parametrise the 'order by' options of a table/group so
that a single report could be ordered by users on run time depending of
their needs?
The alternative is to write/copy the same report once and again and have it
repeated (one instance) for each orderable column.
Regards.Sorry for this post. I just found it on RS' BOL.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:eXv4RWQXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Is it possible to parametrise the 'order by' options of a table/group so
> that a single report could be ordered by users on run time depending of
> their needs?
> The alternative is to write/copy the same report once and again and have
> it repeated (one instance) for each orderable column.
> Regards.
>

Order by "string not empty"?

Hi ng.

I have a varchar field in my table, called Name.

I wanna do a selection, which is ordered by whether this field is empty or
not.

E.g. something like:

SELECT
UserID
ORDER BY
Name <> '';

- - -

How can I accomplish this?

TIA.
Klaus.This will put the non-empty rows first:

SELECT userid
FROM SomeTable
ORDER BY name DESC

--
David Portas
SQL Server MVP
--|||> This will put the non-empty rows first:
> SELECT userid
> FROM SomeTable
> ORDER BY name DESC

It just doesnt seem to be a wise way to do it - why do string sorting when
the only thing needed is whether its empty or not...?|||On Tue, 8 Jun 2004 15:21:36 +0200, Klaus Petersen wrote:

>> This will put the non-empty rows first:
>>
>> SELECT userid
>> FROM SomeTable
>> ORDER BY name DESC
>It just doesnt seem to be a wise way to do it - why do string sorting when
>the only thing needed is whether its empty or not...?

Hi Klaus,

Maybe because ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 2 END is a lot
harder to read and maintain?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||In addition to Hugo's point, using a CASE expression would require that the
expression be evaluated for every single row. Compare performance of the two
approaches and see which works best for you.

--
David Portas
SQL Server MVP
--