SQL can this statement work

[mickywall]

Below is the field structure of my database table 'myTable'

ID (autonumber)
Title (text)
Featured (text)
DateandTime (date/time)

The field featured will either hold the value Y or N

I would like to execute an SQL statement which selects firstly all the records that have a Y in the Featured field - ordered by DateandTime DESC, and then go on and select all the rest of the records and ORDER by DateandTime DESC


Is this possible based on the set up of the table.
Thanks.

 

[FesterSXS]

SELECT * FROM myTable ORDER BY Featured DESC, DateandTime DESC;

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[Lothario]

Try this:

SEELCT ID, Title, Featured, DateandTime FROM myTable
ORDER BY Featured DESC, DateandTime DESC

-L

 

[mickywall]

It doesn't work...
I guess you can't do an ORDER BY statement on a field that either holds the value Y or N

is that right?

 

[Lothario]

Then try this...

create two queries..

qry1

SELECT SEELCT ID, Title, Featured, DateandTime FROM myTable WHERE Featured ='Y' ORDER BY DateandTime DESC

qry2

SELECT SEELCT ID, Title, Featured, DateandTime FROM myTable WHERE Featured ='N' ORDER BY DateandTime DESC

Then do a union query...

SELECT * from qry1
UNION
SELECT * fromqry2

-L

 

[FesterSXS]

mickywall

how did it not work? Did it show an error or were the results not in the order you needed? what order did they appear in that case?

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[Tarwn]

My guess is that they probably just showd up in the wrong order. The responses posted so far assumed (as I would have) that the 'Y' and 'N' examples were in a varchar/text/etc field. If this is in actuality a yes/No access field then you will likely need to order ascending, ie without the DESC. Thats just a guess though, as I don't remember if Access resolves boolean true/yes/etc to -1 or 1 (false is 0).

-T