Dear mhffs-ga,
The easiest solution to this problem is to use a temporary table to
assemble the rescords in the order you desire. I have created a
statement - or more accurately, a set of statements - which does this,
which can be defined as a stored procedure:
CREATE PROCEDURE [getNews]
@category varchar (30)
AS
CREATE TABLE #newstemp (
[title] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[description] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[category] [varchar] (30) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO #newstemp
SELECT [title], [description], [category]
FROM [theNews]
WHERE category LIKE @category
ORDER BY [date] desc
INSERT INTO #newstemp
SELECT [title], [description], [category]
FROM [theNews]
WHERE category NOT LIKE @category
ORDER BY [date] desc
SELECT * FROM #newstemp
DROP TABLE #newstemp
I've tested this against a test theNews table I created on my local
SQL Server to ensure it works. You'll need to adjust the data type of
the category parameter to match your table.
If you run into any problems implementing this solution, or it's not
exactly what you're looking for, please feel free to request a
clarification.
Regards,
cerebrate-ga
Search strategy:
Personal knowlege as DBA.
"Transact-SQL Reference", SQL Server Books Online |
Request for Answer Clarification by
mhffs-ga
on
24 Feb 2003 07:30 PST
I was hoping to find something like:
Orderby category, start with category = "whatIwant"
Possible?
I am running 7 distinct tables on a single web and I am concerned that
the procedure method will slow things down considerably. What do you
think?
Thanks,
Martin
|
Clarification of Answer by
cerebrate-ga
on
24 Feb 2003 08:41 PST
It's a tricky one, certainly.
Unfortunately, none of the SQL variants which I'm familiar with are
quite so obliging as to give you the statement you suggest
(unfortunately - I'd have found it rather useful myself from time to
time), or any close parallels to it. Transact-SQL (Microsoft SQL
Server) certainly doesn't.
With regard to speed - the procedure itself shouldn't affect anything.
The temporary table it uses, on the other hand, well might, depending
on how many rows you have in [theNews]. With the small - thirty-item -
test table I was using, it's not significant, but if your table runs
to thousands of items it probably would be.
(I will say at this point that it also depends on the idiosyncrasies
of the particular database server and underlying machine you're using,
so I'd recommend benchmarking the solution you do decide on before
settling on it.)
It could be optimised somewhat for a large table by only storing the
minimum in the temporary table, and using a join to pull the rest of
the items in order out of the original table:
CREATE PROCEDURE [getNews]
@category varchar (30)
AS
CREATE TABLE #newstemp (
[title] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
)
INSERT INTO #newstemp
SELECT [title]
FROM [theNews]
WHERE category LIKE @category
ORDER BY [date] desc
INSERT INTO #newstemp
SELECT [title]
FROM [theNews]
WHERE category NOT LIKE @category
ORDER BY [date] desc
SELECT [theNews].[title], [theNews].[description],
[theNews].[category]
FROM #newstemp JOIN [theNews]
ON [#newstemp].[title] = [theNews].[title]
DROP TABLE #newstemp
which would minimise the space needed for the temporary table at the
price of needing a JOIN at the last stage. This is likely to be rather
better if [theNews] has a large number of rows.
Another possibility, if [theNews] is very large but the number of
categories is small, is to store the precomputed sort orders in
another table (call it "sortOrder"), something like this:
Columns: SortBy Category Position
A A 1
A B 2
A C 3
B A 2
B B 1
B C 3
C A 2
C B 3
C C 1
where each category is listed as "1" in the group in which it is
listed as "SortBy". You could then achieve the desired sort order
using a query such as:
SELECT [theNews].[title], [theNews].[description], [thenews].[link]
FROM [theNews] JOIN [sortOrder]
ON [theNews].[category] = [sortOrder].[Category]
WHERE [sortOrder].[Category] = 'userInterest'
ORDER BY [sortOrder].[Position], [theNews].[date] desc
This is probably the most efficient solution for a large number of
news items and a relatively small number of categories, but it does
mean you end up with quite a large [sortOrder] table - the number of
rows in it will always be the number of categories squared. It's
almost certainly not a problem from the performance or storage points
of view, as it's a fairly trivial table, but the maintenance of it can
get out of hand if you have a lot of categories.
Hope this was of some help,
cerebrate-ga
|
Request for Answer Clarification by
mhffs-ga
on
24 Feb 2003 13:21 PST
I was thinking maybe a join with an assigned column:
select * newColumn=1, from theNews where category = "whatIwant"
join
select *, newColumn=2, from theNews where category != "whatIwant"
Don't know if this is possible, but if it were then I would:
order by newColumn and the results would work.
What do you think?
|
Clarification of Answer by
cerebrate-ga
on
24 Feb 2003 13:51 PST
Hm. Unfortunately, you can't use a join to concatenate two tables in
that way - only to make relational connections, appending pretty much
requires a temporary table -, but the idea of using a assigned column
suggested a computed column to me, and that can also be made to work:
SELECT *, CASE [category]
WHEN 'userInterest' THEN 1
ELSE 2
END AS [sortBy]
FROM [theNews]
ORDER BY [sortBy]
Do note, though, that ordering by an assigned/computed column will
cause the server to effectively generate a temporary table in the
background anyway to hold the values between computing and sorting, so
it is also probably not going to be too efficient if your [theNews]
table is large.
The other potential drawback here is that CASE isn't required for
ANSI-SQL compliance, and I don't think many products other than
Microsoft SQL Server implement it - and you can't put an IF...ELSE or
a string comparison inside a SELECT statement.
Regards,
cerebrate-ga
|
,
0 Comments
)