July 4, 2008 at 8:18 am
Hi
I have a stored procedure that returns the recent uploaded publications into the database from each user. If only one user uploaded 7 documents at a time I have 7 docs from one user on top but I want to have 1 doc from each which recently uploaded on top. How can it be done.
Here is my SQL SP:
SELECT
ROW_NUMBER() OVER (ORDER BY S.CreationDate desc) AS Row,
P.PUB_TITLE AS 'PubTitle'
FROM
HDS_PUBLICATION P,
HDS_CUSTOM C,
WD_PUBLIC_SHELF S,
HDS_TOPIC T
WHERE
P.PUB_UNID = C.PUB_UNID AND C.CUSTOM3 = 'False'
AND P.PUB_ID = S.PUB_ID
AND P.PUB_UNID = T.PUB_UNID
July 4, 2008 at 8:35 am
You should add the PARTITION BY clause. Since I cannot run your code, I'll show you how to do that with a query that will also run on your DB. This will return the first column name of each table in your db:
[font="Courier New"]SELECT
--TOP 7
* FROM
(
select row_number() OVER(PARTITION BY TABLE_NAME ORDER BY COLUMN_NAME) as RowNum,
TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
) V WHERE RowNum=1
[/font]
Notice the use of "PARTITION BY" clause and the use of derived table (you could also use CTE for that. pretty much the same)
Happy to help
Tal Ben Yosef
July 4, 2008 at 9:31 am
Hi
thank you very much for your reply. I am getting the publications from each user but they are not ordered by creation data but I have the ORDER BY clause on date in my query.
see my SP:
SELECT * FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY S.Contributor ORDER BY S.CreationDate desc) as Row,
P.PUB_TITLE AS 'PubTitle',
S.CreationDate AS 'CreationDate',
S.Contributor AS 'Contributor'
FROM
HDS_PUBLICATION P,
HDS_CUSTOM C,
WD_PUBLIC_SHELF S,
HDS_TOPIC T
WHERE
P.PUB_UNID = C.PUB_UNID AND C.CUSTOM3 = 'False'
AND P.PUB_ID = S.PUB_ID
AND S.Audience ='Public'
AND P.PUB_UNID = T.PUB_UNID
AND S.Contributor != ''
)tmp
WHERE Row = 1
my results are like this: (some how arranged each columns in seperate row, sorry if you can't understand) important thing here is the date, first row was correct 2008-07-04 but the second row brought the 2007---- and third row correct. i think its ordering by the username
1 PROJECT_CONSULT 2008-07-04 11:36:54.543 agulla 1
HN 2007-09-06 14:57:39.360 Alex
1
Schwäbische 2008-07-02 14:33:44.890 aruna 1 logo 2007-12-11 48:03.057 Astair2
July 5, 2008 at 2:32 am
Correct!
and this is expected as the query first sorts by the partition column (contributor) and only then by the order by column (date published).
What you should do is add "order by" after the final WHERE clause (the OUTER one)
Tal
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply