Order by question

How do a construct a select statement that will perform an 'order by ASC',
but will place my nulls at the end of the list instead of the beginning of
the list.
Thanks.
Archerbagman3rd wrote on Wed, 11 May 2005 08:24:01 -0700:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
What's the largest value you expect to see in the column? Place something
larger in the following to replace <<value to use>>
ORDER BY COALESCE(<column>,<<value to use>> )
eg. if column A will have a max value of 100, you would use
ORDER BY COALESCE(A,101)
Dan|||Figure out the "highest" possible value that can be in that column, dependin
g
on what datatype the column is, say it's varchar(6), then it would be
'ZZZZZZ', if it's a smallInt then its 32768, etc.
Then use the Coalesce function
Order By Coalesce(ColNAme, <MaxColValue> )
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
OrderID,
ShippedDate
from
dbo.orders
order by
case when ShippedDate is null then 1 else 0 end asc,
ShippedDate asc
AMB
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer

ORDER BY clause - newbie question

Hi,

Is there any way of passing a variable instead of a hard-coded column name in the ORDER BY clause? E.g.

declare @.OrderCol int
set @.OrderCol = 1 select * from tbl_Box order by @.OrderCol

I know the above code won't run. What I need is be able to determine to sort column at run-time so that instead of writing four different stored procedures with hard-coded order by clauses, I could pass the sort column as an extra parameter to a generic stored procedure. Is that possible at all?

Any help will be appreciated.

Cheers,

Vladislav

Hi Vladislav,

Yes, you can. In your scenario, you would:

declare @.s nvarchar(255),
@.c nvarchar(100)

set @.s = 'select * from tblBox order by '
set @.c = '1' --or 2 or 'BoxNumber' etc.

set @.s = @.s + @.c

exec sp_executesql @.s

Cheers

Rob

|||

Hi Rob,

Thanks a lot. This should certainly help. What I was also looking for is be able to create the following stored procedure

MyDB_sp_GetBoxesByCustomerId [param 1] @.CustomerId int, [param 2] @.SortColumn nvarchar(128)

After some data manipulation, this stored procedure would return a resultset sorted based on the input column name. I would use this stored procedure in my .NET application.

Thanks to your advice, I now know I can build an SQL string and, using sp_exectesql, run it in a .NET program, but I was hoping to find a solution to keep all the 'messy' SQL manipulations inside the stored procedure. Do you think this will be possible?

Once again, thanks your your help.

Cheers,

Vladislav

|||

Hi,

Maybe you can use a construction like:

Select * From Table
Order by Case @.xSort
When 1 Then ColumnName1
When 2 Then ColumnName2
When 3 Then ColumnName3
End

The @.xSort would need to be an input parameter to your procedure

---

Best regards Georg
www.l4ndash.com - Log4net Dashboard / Log4net viewer|||

Thanks alot, Georg. This is certainly a better solution.

Regards,

Vladislav

|||If the possible sort columns are not type-compatible, you will need to do this:

...

order by

case @.xSort when 1 then ColumnName1 end,

case @.xSort when 2 then ColumnName2 end,

case @.xSort when 3 then ColumnName3 end

If you don't do this, the CASE statement will raise an exception the

first time you sort by a column containing a value that cannot be

converted to the highest-precedence type of the three columns.

This version will also avoid unnecessary type conversion in the CASE

statement that could lead to a slower-running query, if an index can't

be used as a result.

Steve Kass

Drew University|||

Drew,

Thanks a bunch. I tried the initial version. As you predicted, I got an exception because my query indeed had a column that could not be converted to the first column. With nothing in MSDN, I was just about to rewrite the stored procedure, when I thought I should check out the forum once more.

Once again, thank a lot.

Cheers,

Vladislav

Order by Clause

Hi,
I have column with values January, February and so on. I need to perform
sort based on months instead the system sorts it by Alphabetical Order. Any
Hint?
Thanks
MannyManny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny
How about storing dates as DATETIME / SMALLDATETIME rather than
strings? If it's too late to do that then you can try:
SELECT mth
FROM tbl
ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
David Portas
SQL Server MVP
--|||do you not have a real date? if not, then do you at least have a year as
well? if not, sorting my month number is pretty meaningless. (e.g. Jan
05 comes after Dec 04).
Manny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny|||Manny Chohan wrote:

> I have column with values January, February and so on. I need to
> perform sort based on months instead the system sorts it by
> Alphabetical Order. Any Hint?
Create a table with months:
Table Months
ID int NOT NULL,
MonthName varchar(20)
and do a join on that. Normally it would be better to save the month
ID instead of the full text.
HTH,
Stijn Verrept.|||... order by
charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Augus
t*September*October*November*December*')
Steve Kass
Drew University
Manny Chohan wrote:

>Hi,
>I have column with values January, February and so on. I need to perform
>sort based on months instead the system sorts it by Alphabetical Order. Any
>Hint?
>Thanks
>Manny
>|||Thanks. It worked.
"Steve Kass" wrote:

> ... order by
> charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Aug
ust*September*October*November*December*
')
> Steve Kass
> Drew University
> Manny Chohan wrote:
>
>|||I apologize for the off-topic comment, but this one is just too good to pass
:

> do you not have a real date?
How many times have I been asked that question - not necesarily in the same
context, but still... :)
ML
http://milambda.blogspot.com/|||David with all due respect, I'd suggest a datetime format that carries the
century. Yes, you specify the year 2000 but we should all be developing cod
e
that doesn't leave ambiguity because you never know what SQL Server defaults
will be in the future and more impportantly how your technique will be
applied to other situations.
CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
And, we should be thinking globally so it really should be:
CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
Just my two cents,
Joe
"David Portas" wrote:

> Manny Chohan wrote:
> How about storing dates as DATETIME / SMALLDATETIME rather than
> strings? If it's too late to do that then you can try:
> SELECT mth
> FROM tbl
> ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
> --
> David Portas
> SQL Server MVP
> --
>|||Joe from WI wrote:
> David with all due respect, I'd suggest a datetime format that carries the
> century. Yes, you specify the year 2000 but we should all be developing c
ode
> that doesn't leave ambiguity because you never know what SQL Server defaul
ts
> will be in the future and more impportantly how your technique will be
> applied to other situations.
> CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
>
A good point.

> And, we should be thinking globally so it really should be:
> CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
>
That does work although on the face of it the string is wrong. 112
defines the format as YYYYMMDD, which is not what you have specified.
There is some implict conversion at work here so I'd stick to the 101
version because the string that's passed complies with the documented
format and behaviour for CONVERT.
David Portas
SQL Server MVP
--sql

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.You don't need to use temp tables and subqueries here. The example
below will do the job, assuming that ID is the primary key and is never
changed. Note that you'll also need to include other columns in the
UPDATE statement in order for those to be updated.

CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DTB" <macdtb@.mac.com> wrote in message
news:4af8df45.0308170552.27c24433@.posting.google.c om...
> I am having trouble creating an INSTEAD OF trigger in SQL Server to
> replicate a BEFORE UPDATE trigger from ORACLE.
> Here is a sample of the ORACLE BEFORE UPDATE trigger:
> CREATE TRIGGER myTRIGGER ON MYTABLE
> begin
> :new.DT := SYSDATE;
> if :new.NM is NULL then
> :new.NM := USER;
> end if;
> end myTRIGGER;
> It seems as though I have to jump through hoops in SQL Server AND I
> cannot come up with correct results.
> Here is a snippet from SQL SERVER (this is what I figured I needed to
> do after reading various articles,questions):
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> SELECT * INTO #MYTABLE FROM INSERTED
> UPDATE #MYTABLE SET DT = GETDATE()
> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> UPDATE THETABLE
> SET
> DT = (SELECT DT FROM #MYTABLE),
> NM = (SELECT NM FROM #MYTABLE)
> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.|||Thank you Dan! This works the way I want it to.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<ZfN%a.27352$vo2.5843@.newsread1.news.atl.earthlink. net>...
> You don't need to use temp tables and subqueries here. The example
> below will do the job, assuming that ID is the primary key and is never
> changed. Note that you'll also need to include other columns in the
> UPDATE statement in order for those to be updated.
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> UPDATE THETABLE
> SET
> DT = GETDATE(),
> NM = COALESCE(inserted.NM, USER)
> FROM inserted
> WHERE THETABLE.ID = inserted.ID
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --------
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------
> "DTB" <macdtb@.mac.com> wrote in message
> news:4af8df45.0308170552.27c24433@.posting.google.c om...
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> > Can anyone please shed some light on this? Thanks in advance.|||I'm tring to import the data from text file into Table and table have
the trigger. but it takes too long time

here is code

CREATE TRIGGER TR_TFACP200

ON TFACP200

FOR INSERT

AS

UPDATE TFACP200

SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)

WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1

AND DOCUMENT_DATE IS NULL

can any give me the solution, i thing this is happening boz. UPDATE
statement is fire on all records when we insert a single record.

I think if i use BEFORE INSERT trigger like ORACLE then it solve the
problem, but how i use the BEFORE INSERT trigger in SQL server

I'm using SQL Server 7.0, and INSTEAD OF option not available.

pls help me.

Milind

I am having trouble creating an INSTEAD OF trigger in SQL Server version
7.0 to replicate a BEFORE INSERT trigger from ORACLE.

Originally posted by Dtb

> I am having trouble creating an INSTEAD OF trigger in SQL Server to

> replicate a BEFORE UPDATE trigger from ORACLE.

> Here is a sample of the ORACLE BEFORE UPDATE trigger:

> CREATE TRIGGER myTRIGGER ON MYTABLE

> begin

> :new.DT := SYSDATE;

> if :new.NM is NULL then

> :new.NM := USER;

> end if;

> end myTRIGGER;

> It seems as though I have to jump through hoops in SQL Server AND I

> cannot come up with correct results.

> Here is a snippet from SQL SERVER (this is what I figured I needed to

> do after reading various articles,questions):

> CREATE TRIGGER myTRIGGER on THETABLE

> INSTEAD OF UPDATE

> AS

> SELECT * INTO #MYTABLE FROM INSERTED

> UPDATE #MYTABLE SET DT = GETDATE()

> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

> UPDATE THETABLE

> SET

> DT = (SELECT DT FROM #MYTABLE),

> NM = (SELECT NM FROM #MYTABLE)

> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.

--
Posted via http://dbforums.com|||Milind,

you are correct that the current problem is, that your trigger acts on
all rows of the table instead of just the inserted.

In the trigger context, all inserted rows are available in the virtual
table "inserted". (Lookup "triggers, inserted tables" in BOL for more
information).

Assuming "id" is the primary key of your table, you could use:

CREATE TRIGGER TR_TFACP200
ON TFACP200
FOR INSERT
AS

UPDATE TFACP200
SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)
WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1
AND DOCUMENT_DATE IS NULL
AND EXISTS (
SELECT 1
FROM inserted
WHERE inserted.id = TFACP200.id
)

Hope this helps,
Gert-Jan

Milind wrote:
> I'm tring to import the data from text file into Table and table have
> the trigger. but it takes too long time
> here is code
> CREATE TRIGGER TR_TFACP200
> ON TFACP200
> FOR INSERT
> AS
> UPDATE TFACP200
> SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)
> WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)) = 1
> AND DOCUMENT_DATE IS NULL
> can any give me the solution, i thing this is happening boz. UPDATE
> statement is fire on all records when we insert a single record.
> I think if i use BEFORE INSERT trigger like ORACLE then it solve the
> problem, but how i use the BEFORE INSERT trigger in SQL server
> I'm using SQL Server 7.0, and INSTEAD OF option not available.
> pls help me.
> Milind
> I am having trouble creating an INSTEAD OF trigger in SQL Server version
> 7.0 to replicate a BEFORE INSERT trigger from ORACLE.
> Originally posted by Dtb
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.
> --
> Posted via http://dbforums.com