Hello.
I want to display some data from a database in a certain order. I have a table with columns A, B, Date. I want to display the records that have data in column A first and I want those records to be in ASC order of Date(the data should already be in that order because it's the order that the records are inserted and all the new records get inserted at the bottom. And then I want to display the rest (the records that have '' in column A) in Date order. So I did this.....
SELECT A, B, Date FROM TABLE WHERE A <> '' UNION ALL SELECT A, B, Date FROM TABLE WHERE A=''
At first it was working fine, but for some reason the order started getting mixed up so I changed it to this...
(SELECT A, B, Date FROM TABLE WHERE A <> '' ORDER BY Date) UNION ALL (SELECT A, B, Date FROM TABLE WHERE A='' ORDER BY Date)
The order is still wrong. If I do one select SELECT A, B, Date FROM TABLE WHERE A <> '' ORDER BY Date --it's in the right order and when I do SELECT A, B, Date FROM TABLE WHERE A='' ORDER BY Date --that also displays right
It gets mixed up when I put them together with UNION ALL. The ones that have data in column A are still displayed on top but in the wrong order....and below that, the ones with nothing are displayed, but not in order.
Please help me=)
Thank you.
4  3693 
Hi
Hello.
I want to display some data from a database in a certain order. I have a table with columns A, B, Date. I want to display the records that have data in column A first and I want those records to be in ASC order of Date(the data should already be in that order because it's the order that the records are inserted and all the new records get inserted at the bottom. And then I want to display the rest (the records that have '' in column A) in Date order. So I did this.....
SELECT A, B, Date FROM TABLE WHERE A <> '' UNION ALL SELECT A, B, Date FROM TABLE WHERE A=''
At first it was working fine, but for some reason the order started getting mixed up so I changed it to this...
(SELECT A, B, Date FROM TABLE WHERE A <> '' ORDER BY Date) UNION ALL (SELECT A, B, Date FROM TABLE WHERE A='' ORDER BY Date)
The order is still wrong. If I do one select SELECT A, B, Date FROM TABLE WHERE A <> '' ORDER BY Date --it's in the right order and when I do SELECT A, B, Date FROM TABLE WHERE A='' ORDER BY Date --that also displays right
It gets mixed up when I put them together with UNION ALL. The ones that have data in column A are still displayed on top but in the wrong order....and below that, the ones with nothing are displayed, but not in order.
Please help me=)
Thank you.
You could try this
SELECT A, B, [Date], 1 as SortKey FROM TABLE WHERE A <> ''
UNION ALL
SELECT A, B, [Date], 2 as SortKey FROM TABLE WHERE A=''
ORDER BY SortKey, [Date]
and see what happens?
MTB
Hello.
I want to display some data from a database in a certain order. I have a table with columns A, B, Date. I want to display the records that have data in column A first and I want those records to be in ASC order of Date(the data should already be in that order because it's the order that the records are inserted and all the new records get inserted at the bottom. And then I want to display the rest (the records that have '' in column A) in Date order. So I did this.....
SELECT A, B, Date FROM TABLE WHERE A <> '' UNION ALL SELECT A, B, Date FROM TABLE WHERE A=''
At first it was working fine, but for some reason the order started getting mixed up so I changed it to this...
(SELECT A, B, Date FROM TABLE WHERE A <> '' ORDER BY Date) UNION ALL (SELECT A, B, Date FROM TABLE WHERE A='' ORDER BY Date)
The order is still wrong. If I do one select SELECT A, B, Date FROM TABLE WHERE A <> '' ORDER BY Date --it's in the right order and when I do SELECT A, B, Date FROM TABLE WHERE A='' ORDER BY Date --that also displays right
It gets mixed up when I put them together with UNION ALL. The ones that have data in column A are still displayed on top but in the wrong order....and below that, the ones with nothing are displayed, but not in order.
Please help me=)
Thank you.
Hi, there.
If you want to place records where A field is empty to the bottom of query return while sorting the rest records by [Date] field, then, I think, UNION is not necessary.
Try the following query. -
SELECT Table.A, Table.B, Table.[Date]
-
FROM Table
-
ORDER BY IIf(IsNull([Table]![A]) Or [Table]![A]='',0,1) DESC, Table.[Date];
-
And, G..d save us, if you can't avoid using reserved word "Date" as field name, then enclose it in square brackets at least. ;)
Hi
You could try this
SELECT A, B, [Date], 1 as SortKey FROM TABLE WHERE A <> ''
UNION ALL
SELECT A, B, [Date], 2 as SortKey FROM TABLE WHERE A=''
ORDER BY SortKey, [Date]
and see what happens?
MTB
Whoohoo! That works! Thank you very much!
Hi, there.
If you want to place records where A field is empty to the bottom of query return while sorting the rest records by [Date] field, then, I think, UNION is not necessary.
Try the following query.
-
SELECT Table.A, Table.B, Table.[Date]
-
FROM Table
-
ORDER BY IIf(IsNull([Table]![A]) Or [Table]![A]='',0,1) DESC, Table.[Date];
-
And, G..d save us, if you can't avoid using reserved word "Date" as field name, then enclose it in square brackets at least. ;)
Hello! Thank you!
I actually don't use 'Date' for the field name. I just thought it would be easier to understand. But I guess if I used a keyword to describe it, that might be the problem and...I guess it changes the situation......but anyway.. sorry about that.
It would be nice to make my sql statement shorter so I will give your suggestion a try.
Thank you again!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: KENNY L. CHEN |
last post by:
Dear experts,
I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).
Both tables are unique-indexed on (COL1,COL2,REC_NO).
I think the following...
|
by: Adrian |
last post by:
Dear All
I've come across a strange issue when using UNION via MyODBC on my
Fedora linux box (I'm using MySQL 4.1.3-standard-beta)
**This doesn't work:
(select a from tbl_name where a=10...
|
by: Blake Caraway |
last post by:
All,
I've seen several posts regarding using UNION or UNION ALL to mash
together two or more resultsets into a single result set, but can't
seem to find enough info here to help me answer my...
|
by: Tom Schindl |
last post by:
Hi,
the following Statement worked on MySQL 4.0 but after upgrading to
4.1.12 on win32 the order is not working any more. Is this a known
problem or is our SQL simply not useable on 4.1 or is...
|
by: NAJH |
last post by:
I've been trying to do a union with a subquery - I've made a different
example which follows the same principles as follows:
First bit brings back accounts which are in the top 10 to 15 by...
|
by: Jeff Blee |
last post by:
I hope someone can help me get this graph outputing in proper order.
After help from Tom, I got a graph to display output from the previous
12 months and include the average of that output all in...
|
by: Alicia |
last post by:
Hello everyone based on the data, I created a union query which
produces this.
SELECT ,,, 0 As ClosedCount
FROM
UNION SELECT
,, 0 AS OpenedCount,
FROM
ORDER BY , ;
|
by: S. van Beek |
last post by:
Dear reader,
The following code delivers a wild card in the result of the query.
But the ORDER BY is not longer working.
|
by: kigoobe |
last post by:
Hi friends, I'm having three queries that works perfectly ...
SELECT ib.id as id, ib.titre as title, ib.date_expire as date_fin, ib.created_at as date_creation, eb.content as content, ''
FROM...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| | |
