Union/Combined results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union/Combined results

Author

Topic

infra
Starting Member

5 Posts

Posted - 2009-10-16 : 02:50:41

Hi guys,

First time poster.

I'm a little,... well very rusty, with my SQL. Figured you experts out there would have an idea as to how to solve my problem here.

Little background on the database:
-It's a historical database with events and people from time; each record has a name, description, a start year, an end year, startYear suffix*, endYear suffix*
*Suffix = BC or AD

Corresponding with the above information, here is an example record:

Example#1:

John Doe | Famous Musician born in the 19th century | 1839 | 1910 | AD | AD

Example2:

Roman Period | The Roman Empire | 63 | 476 | BC | AD

The problem:
Now, this is simple enough, but I have BC dates as well. So there could technically be an 1839 AD as well as an 1839 BC date.

Figuring out which record to return (the AD or BC) is not a problem by itself, but when I have to return a set of records say from the year 2000BC to 2000AD, you can see that things might not be so clean cut if I simply order the records via ORDER BY ASC/DESC in relation to the starting year of the record0.

Since the years aren't signed (1839 vs -1839) it's not going to work well. However, what I do have to work with is the fact that I have the BC/AD designation for each record.

I'm hoping there is a way to do "two" selects and combine the results where if I searched from 2000BC to 2000AD, the first part would return all records which years are from a BC date, in this case 2000BC, ordered in a descending fashion all the way to 0.
Then, combine the results with another search that selects all records greater than the year 0 (AD dates in this case) ordered in an ascending fashion.

You'd get records that show years like such:
2000 BC, 1500 BC, 10 BC, 15AD, 1500 AD, 1993 AD, 2009 AD

So in SQL terms, the statement would look like follows (of course this statement does NOT work, but you get the idea of what I'm trying to do):

select * from tablename WHERE startYearSuffix = 'BC' ORDER BY event_Start DESC
UNION
select * from tablename WHERE startYearSuffix = 'AD' ORDER BY event_Start ASC;

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-16 : 03:30:55

Something like this might work:

select * from tablename ORDER BY CASE WHEN startYearSuffix = 'AD' then -1 * event_Start ELSE event_Start END

- Lumbago
http://xkcd.com/327/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-16 : 03:45:47

If you'd have the AD/BC column as a bit with the values 0 or 1 (i.e "IsAD") you could get rid of the case:

select * from tablename ORDER BY event_Start * ((IsAD * 2) - 1)

This is a trick I learned some years ago...I think it was MVJ that taught it to me here in this forum.

- Lumbago
http://xkcd.com/327/

infra
Starting Member

5 Posts

Posted - 2009-10-16 : 14:35:31

quote:
Originally posted by Lumbago

Something like this might work:

select * from tablename ORDER BY CASE WHEN startYearSuffix = 'AD' then -1 * event_Start ELSE event_Start END

- Lumbago
http://xkcd.com/327/

By the looks of it this worked!

Could you explain the select statement? What does the -1 switch do?

To make matters a little more complicated, I also have a MYA suffix which predates BC. So I'd have to do the same thing more or less with what we did here with BC and AD.

i.e.

200 MYA, 150 MYA, 2000BC, 1500BC, 1500AD, 1999AD etc.

Thank you for the help Lumbago!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-19 : 03:02:12

It's really easy actually...what it does is to multiply the year with -1 if the year is AD and if not then just leave it as it is. And naturally when sorting negative numbers ascending you will get the "largest" years first. Just include the case statement in the select and look at the output:

select 
  SortColumn = CASE WHEN startYearSuffix = 'AD' then -1 * event_Start ELSE event_Start END,
  * 
from tablename 
ORDER BY SortColumn

- Lumbago
http://xkcd.com/327/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-19 : 03:29:18

And with the MYA you need to add another case and make sure that the product of the multiplication is smaller than all the numbers in the event_Start column:

select 
  SortColumn = CASE 
    WHEN startYearSuffix = 'MYA' then -1000000 * event_Start 
    WHEN startYearSuffix = 'AD' then -1 * event_Start 
    ELSE event_Start 
    END,
  * 
from tablename 
ORDER BY SortColumn

- Lumbago
http://xkcd.com/327/

infra
Starting Member

5 Posts

Posted - 2009-10-19 : 17:36:56

quote:
Originally posted by Lumbago

And with the MYA you need to add another case and make sure that the product of the multiplication is smaller than all the numbers in the event_Start column:
select
SortColumn = CASE
WHEN startYearSuffix = 'MYA' then -1000000 * event_Start
WHEN startYearSuffix = 'AD' then -1 * event_Start
ELSE event_Start
END,
*
from tablename
ORDER BY SortColumn

- Lumbago
http://xkcd.com/327/

Lumbago, thank you for the explanation.

I prepared the SQL statement as such and it seems to work (I couldn't get it to work with the SortColumn variable you have in your example):

SELECT * FROM mydb.events ORDER BY CASE
WHEN event_startYearSuffix = 'MYA' THEN -1000000 * event_Start
WHEN event_startYearSuffix = 'AD' THEN -1 * event_Start
ELSE event_Start
END;

This seems to work. I also changed the value of -1000000 to 1000000 which ordered the MYA dates appropriately.

I have a last question regarding this particular select statement.

How can I effectively return ranges of years?

i.e.

5000 MYA to 20000 BC
14000 MYA to 2010 AD

Thank you again for your help. I really appreciate this.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-20 : 04:04:44

How many rows do you have in this events-table? The reason I'm asking is that when you want to filter based on "varying" criteria like this the performance can suffer quite a bit. If you have millions of rows you're bound to get performance problems unless you do some modifications to the table.

To get some decent performance out of a BETWEEN like this you'll probably need to create a column in the table similar to the SortColumn we created, with an ordered "fake" representation of event_Start and startYearSuffix. To do this you can create a user defined function to help you out in the inserts/selects and the function could look like this:

CREATE FUNCTION dbo.SortColumn (
  @event_Start int, 
  @startYearSuffix varchar(3))
RETURNS int
AS
BEGIN
  DECLARE @SortColumn int
  SELECT @SortColumn = CASE
    WHEN @startYearSuffix = 'MYA' THEN -1000000 * @event_Start
    WHEN @startYearSuffix = 'AD' THEN -1 * @event_Start
    ELSE @event_Start
    END
    
	RETURN @SortColumn
END

Then when you insert new rows to the table you could do like this:

INSERT INTO events SELECT 5000, 'MYA', dbo.SortColumn(5000, 'MYA')
INSERT INTO events SELECT 20000, 'BC', dbo.SortColumn(20000, 'BC')

And then when you would like to search for a range in the table you could simply do a

SELECT * FROM events WHERE SortColumn BETWEEN dbo.SortColumn(5000, 'MYA') AND dbo.SortColumn(20000, 'BC')

If you also create an index on the SortColumn your query would be very effective and also easy to maintain.

- Lumbago
http://xkcd.com/327/

infra
Starting Member

5 Posts

Posted - 2009-10-20 : 19:47:12

I have approximately 8000 records in the events table.

It does seem to take a bit of a performance hit when I search the entire database.

I'm just reading the rest of your response here and I'm trying to understand it. I'm having trouble translating your example into MySQL Query Browser. I'm creating a new function correct?

I think I may be messing up on the delimiters or something but I'm trying to put this in MySQL as a function:


DELIMITER $$

DROP FUNCTION IF EXISTS `mydb`.`sortColumn` $$
CREATE FUNCTION `mydb`.`SortColumn` (@event_Start int, @event_startYearSuffix varchar(5)) RETURNS INT
AS
BEGIN
  DECLARE @SortColumn INT
  SELECT @SortColumn = CASE
    WHEN @event_startYearSuffix = 'MYA' THEN 1000000 * @event_Start
    WHEN @event_startYearSuffix = 'AD' THEN -1 * @event_Start
    ELSE @event_Start
    END
    RETURN @SortColumn
END $$
DELIMITER ;

The error message isn't exactly revealing:

Script line: 4	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@event_Start int, @event_startYearSuffix varchar(5)) RETURNS INT
AS
BEGIN
  D' at line 1

A side note:
An additional foreseeable problem I may encounter are identical records as each event could technically be in multiple categories (i.e. Hitler may be in categories "War" and "Conquerors"). I'm assuming I would use the "group by" functionality where if I have multiple Hitler events, it would simply show one.

Example records that would be duplicate in nature that are different by categorical classification:

Elvis Presley | 1935 | 1977 | Singer
Elvis Presley | 1935 | 1977 | Actor

I'd essentially want to show one of the two records. I'm not entirely picky about which one shows if that matters.

Thank you once again. I appreciate the time you're taking to provide me with these great examples.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-21 : 03:13:54

Umh...mentioning that you're working on MySQL is a vital piece of information that you left out! This is an MS SQL Server forum only and I have no idea how to implement functions in MySQL. You'll have to read the documentation: http://dev.mysql.com/doc/refman/5.1/en/create-function-udf.html I guess the concept is somewhat similar.

- Lumbago
http://xkcd.com/327/

infra
Starting Member

5 Posts

Posted - 2009-10-21 : 13:54:43

Yikes, I honestly had no idea this was an MS SQL only forum lol

Well either way your advice worked very well and was more or less seamless when using MySQL.

I'll take a look at the documentation to create the function you gave me.

Thanks.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-22 : 04:15:36

No worries, and good luck on the implementation!

- Lumbago
http://xkcd.com/327/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources