The Revenge of the Difficult Sort

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

SQL Server 2000 Forums

SQL Server Development (2000)

The Revenge of the Difficult Sort

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2002-12-13 : 11:05:07

Hi everyone

I'm in a real situation, just launched a new news system we've been working on the last few months, which everything seemed to be fine on. But now suddenly a problem has cropped up.
It's in the way stories are listed in the admin area.

Basically the database has two tables for news, one contains the news stories and the other contains the updates to those stories.
So the stored procedure in the admin area says
* Make a temp table
* Insert into the table all the news stories
* Insert into the table all the news updates
* Display the temp table

Ok, now here's the science bit :)

We want the list be be sorted so that the newest story is listed first ( NewsDate DESC ), then all the updates for that story are listed underneath it in order of date ( again NewsDate DESC ).
So basically that resultset should look like this

( NewsType: 1 = News Story, 2 = News Update )
( NewsID: The unique ID of the news story, referenced by the updates )

NewsType NewsDate NewsID
1 2002-10-5 10
2 2002-8-4 10
2 2002-7-4 10
1 2002-5-3 8
1 2002-4-3 14
2 2002-4-6 14

etc...
So that structure... so if a story has an update adding to it, it will jump to the top.
So if I added an update to NewsID 14, this would happen

NewsType NewsDate NewsID
1 2002-4-3 14
2 2002 12-10 14
2 2002-4-6 14
1 2002-10-5 10
2 2002-8-4 10
2 2002-7-4 10
1 2002-5-3 8

The SQL I'm using to sort this table is

SELECT
NewsType,
NewsDatetime,
NewsID
FROM
#TableTempAdminList
ORDER BY
(
SELECT
MAX( NewsDatetime )
FROM #TableTempAdminList
WHERE #TableTempAdminList.NewsID = TableTempAdminList.NewsID
) ,
NewsID DESC,
NewsType ASC,
NewsDatetime DESC

Which seemed to work fine in testing, but just doesn't anymore

Any help would be very very welcome!

Regards
Pete

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-13 : 15:13:27

SELECT A.NewsDate, A.NewsID, A.NewsType
FROM
NewsStories N
INNER JOIN
NewsStories A
ON A.NewsID = N.NewsID
WHERE
N.NewsType = 1
ORDER BY
N.NewsDate DESC, A.NewsDate DESC

- Jeff

philh
Starting Member

18 Posts

Posted - 2002-12-14 : 11:35:59

Maverick,

[ed. Stand by for more; this works for the latest but doesn't order the rest properly. -ph]
In my example, it_newsID = newsID; it_level = NewsType; it_date = NewsDate; news_items = #TableTempAdminList.
[ed. OK, this should work]
SELECT A.it_date, A.it_NewsID, A.it_level,
case when a.it_date = (select DISTINCT max(b.it_date) from news_items b)
then 0 else 1 end
as latest_item,
case when a.it_newsid IN(select DISTINCT c.it_newsid from news_items c
where c.it_date = (select DISTINCT max(d.it_date)
from news_items d))
then 0 else 1 end
as latest_group,

(select distinct top 1 it_date from news_items e where e.it_newsid = a.it_newsid and e.it_level = 1)
as list_order_date

FROM
News_items A

group by a.it_newsid, a.it_level, a.it_date, a.it_level
ORDER BY
latest_group, list_order_date DESC, it_level, it_newsID, latest_item, it_date DESC

HTH,
PH

Phil Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
The best thermal transfer printer ribbons on the planet

Edited by - philh on 12/14/2002 11:40:28

Edited by - philh on 12/14/2002 11:57:40

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-14 : 15:19:20

CREATE TABLE #News(
NewsID int,
NewsType int,
NewsDate datetime
)

INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (14, 1, '20020403')
INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (14, 2, '20021210')
INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (14, 2, '20020406')
INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (10, 1, '20021005')
INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (10, 2, '20020804')
INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (10, 2, '20020704')
INSERT INTO #News (NewsID, NewsType, NewsDate) VALUES (8, 1, '20020503')

SELECT A.NewsID, A.NewsType, A.NewsDate
FROM
#News A INNER JOIN
(
SELECT NewsID, MAX(NewsDate) AS MaxNewsDate
FROM #News
GROUP BY NewsID
) B ON A.NewsID = B.NewsID
ORDER BY B.MaxNewsDate Desc, A.NewsID , A.NewsType

DROP TABLE #News

Edited by - ValterBorges on 12/14/2002 15:19:44

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2002-12-16 : 04:25:07

Cheers guys, you're life savers!

Now to get back to the list of the other 20 problems that has cropped up ;)

Tim
Starting Member

392 Posts

Posted - 2002-12-16 : 21:57:58

Maverick I hope you didn't implement these without testing!

See below (using ValterBorges' DDL thank you very much !)


-- jsmith8588
--    returns the news "stories" in correct sequence ?  NO
--    returns the news "updates" in correct sequence within news "stories" ?  NO
SELECT A.NewsDate, A.NewsID, A.NewsType 
FROM 
#News N 
INNER JOIN 
#News A 
ON A.NewsID = N.NewsID 
WHERE 
N.NewsType = 1 
ORDER BY 
N.NewsDate DESC, A.NewsDate DESC 

/*
NewsDate                                               NewsID      NewsType    
------------------------------------------------------ ----------- ----------- 
2002-10-05 00:00:00.000                                10          1
2002-08-04 00:00:00.000                                10          2
2002-07-04 00:00:00.000                                10          2
2002-05-03 00:00:00.000                                8           1
2002-12-10 00:00:00.000                                14          2
2002-04-06 00:00:00.000                                14          2
2002-04-03 00:00:00.000                                14          1
*/


-- ValterBorges
--    returns the news "stories" in correct sequence ?  YES
--    returns the news "updates" in correct sequence within news "stories" ?  NO
SELECT A.NewsID, A.NewsType, A.NewsDate 
FROM 
#News A INNER JOIN 
( 
SELECT NewsID, MAX(NewsDate) AS MaxNewsDate 
FROM #News 
GROUP BY NewsID 
) B ON A.NewsID = B.NewsID 
ORDER BY B.MaxNewsDate Desc, A.NewsID , A.NewsType 

/*
NewsID      NewsType    NewsDate                                               
----------- ----------- ------------------------------------------------------ 
14          1           2002-04-03 00:00:00.000
14          2           2002-04-06 00:00:00.000
14          2           2002-12-10 00:00:00.000
10          1           2002-10-05 00:00:00.000
10          2           2002-07-04 00:00:00.000
10          2           2002-08-04 00:00:00.000
8           1           2002-05-03 00:00:00.000
*/


-- PhilH
--    you lost me there  :-)



-- Tim
--    returns the news "stories" in correct sequence ?  YES
--    returns the news "updates" in correct sequence within news "stories" ?  YES
--
-- Same approach as VolterBorges but just tweaking the ORDER BY so that it is correct
-- and also not depending on IDs being in any particular order, just in case ;-)
SELECT 
 A.NewsType, A.NewsDate, A.NewsID 
FROM 
 #NEWS AS A
 JOIN (SELECT NewsID, MostRecentNewsDate = max(NewsDate) FROM #News GROUP BY NewsID) AS B
  ON A.NewsID = B.NewsID
ORDER BY 
 B.MostRecentNewsDate DESC, A.NewsType, A.NewsDate DESC

/*
NewsType    NewsDate                                               NewsID      
----------- ------------------------------------------------------ ----------- 
1           2002-04-03 00:00:00.000                                14
2           2002-12-10 00:00:00.000                                14
2           2002-04-06 00:00:00.000                                14
1           2002-10-05 00:00:00.000                                10
2           2002-08-04 00:00:00.000                                10
2           2002-07-04 00:00:00.000                                10
1           2002-05-03 00:00:00.000                                8
*/

Mark-up SQLTeam posts here
http://www.markitup.com/Forum/SQLTeam.asp

Edit - I notice that updates to news story 10 were published before the story itself. (?)

Perhaps the reason it "stopped working" for you is that you tested with valid data, but in production the dates are screwed up. Although the above will still order the records correctly it will look funny when displayed...

I also worry that if you have large amount of news this will be a dog. First you have to create the temp table and second you won't have indexes to help the queries. I would go with view(s) rather than temp table.

Edited by - tim on 12/16/2002 22:15:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources