Order By

Have data in a view in the following format:
DateType Date FileNumber
-- -- --
Order 1/1/01 00000001
Invoice 2/2/02 00000001
Inspection 3/3/03 00000001
... ... 00000002
Not all FileNumbers will have a record with a DateType of "Order", but I
need to order by this (no pun intended). Essentially I want to order by
date desc, if the DateType is "Order"...and leave all the file numbers
without an OrderDate at the bottom. I'm sure this is possible, I'm just not
the SQL guy around here, and he's on vacation.
Any suggestions?
Thanks,
JamesSELECT * FROM <tablename> WHERE DateType = 'Order' ORDERY BY Date DESC
This should work. Let me know if you get what you are looking for.
HTH
Bhanu.
"James" <cppjames@.aol.com> wrote in message
news:ekf$Fq5tEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Have data in a view in the following format:
> DateType Date FileNumber
> -- -- --
> Order 1/1/01 00000001
> Invoice 2/2/02 00000001
> Inspection 3/3/03 00000001
> ... ... 00000002
>
> Not all FileNumbers will have a record with a DateType of "Order", but I
> need to order by this (no pun intended). Essentially I want to order by
> date desc, if the DateType is "Order"...and leave all the file numbers
> without an OrderDate at the bottom. I'm sure this is possible, I'm just
not
> the SQL guy around here, and he's on vacation.
> Any suggestions?
> Thanks,
> James
>
>|||The problem lies in the fact that I need the other records as well...the
ones with the Inspected and Invoiced dates.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23gKYZ45tEHA.1008@.tk2msftngp13.phx.gbl...
> SELECT * FROM <tablename> WHERE DateType = 'Order' ORDERY BY Date DESC
>
> This should work. Let me know if you get what you are looking for.
> HTH
> Bhanu.
> "James" <cppjames@.aol.com> wrote in message
> news:ekf$Fq5tEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Have data in a view in the following format:
> >
> > DateType Date FileNumber
> > -- -- --
> > Order 1/1/01 00000001
> > Invoice 2/2/02 00000001
> > Inspection 3/3/03 00000001
> > ... ... 00000002
> >
> >
> > Not all FileNumbers will have a record with a DateType of "Order", but I
> > need to order by this (no pun intended). Essentially I want to order by
> > date desc, if the DateType is "Order"...and leave all the file numbers
> > without an OrderDate at the bottom. I'm sure this is possible, I'm just
> not
> > the SQL guy around here, and he's on vacation.
> >
> > Any suggestions?
> >
> > Thanks,
> > James
> >
> >
> >
> >
>|||Try this unless you get better answers
SELECT * INTO #temp FROM [Order]
WHERE datetype = 'order'
ORDER BY Date DESC
SELECT * FROM #temp
UNION ALL
SELECT * FROM [Order]
WHERE datetype <> 'order'
Thanks
GYK
"Bhanu" wrote:
> SELECT * FROM <tablename> WHERE DateType = 'Order' ORDERY BY Date DESC
>
> This should work. Let me know if you get what you are looking for.
> HTH
> Bhanu.
> "James" <cppjames@.aol.com> wrote in message
> news:ekf$Fq5tEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Have data in a view in the following format:
> >
> > DateType Date FileNumber
> > -- -- --
> > Order 1/1/01 00000001
> > Invoice 2/2/02 00000001
> > Inspection 3/3/03 00000001
> > ... ... 00000002
> >
> >
> > Not all FileNumbers will have a record with a DateType of "Order", but I
> > need to order by this (no pun intended). Essentially I want to order by
> > date desc, if the DateType is "Order"...and leave all the file numbers
> > without an OrderDate at the bottom. I'm sure this is possible, I'm just
> not
> > the SQL guy around here, and he's on vacation.
> >
> > Any suggestions?
> >
> > Thanks,
> > James
> >
> >
> >
> >
>
>|||On Thu, 21 Oct 2004 14:58:35 -0400, James wrote:
>The problem lies in the fact that I need the other records as well...the
>ones with the Inspected and Invoiced dates.
Hi James,
Nasty, nasty.
This is untested (as you didn't provide CREATE TABLE and INSERT statements
for me to base my tests on), but it might work.
SELECT a.DateType, a.Date, a.FileNumber
FROM YourTable AS a
LEFT JOIN YourTable AS b
ON b.FileNumber = a.FileNumber
AND b.DateType = 'Order'
ORDER BY b.DateType DESC
, a.DateType ASC -- Big assumption here!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)