I would like to merge WITH clause and SELECT clause so the both will be within one table query.

One of them "downloadinfo" contains data about time of downloading some file by a specified user and the other is an output of file details, joined with two other tables (category and once again download info but only for a download counter purpose).

Select counter

SELECT FileAlias, FileEXT, FileSize, FileExtension, UploadDate, category, COUNT(id_downloadInfo) as "counter"
FROM files 
JOIN categories ON files.category_id = categories.id_categories 
JOIN users ON files.users_id_users = users.id_users 
LEFT JOIN downloadinfo ON files.id_files = downloadinfo.files_id_files
WHERE organisations_id_organisations = 1 AND UserOnly = 0
GROUP BY files_id_files;

It produces result like below

And then I have the other query that I would like to merge with this resulted query. Basically each time someone downloads a file counter increments by 1 as a other SQL query to table download file. It passess downloading user id, downloading file id and datetime of the download.

I wanted to filter latest (or newest) download time so I made another query.

WITH added_row_number AS (
    SELECT files_id_files, DownloadDate, ROW_NUMBER() OVER(PARTITION BY files_id_files 
    ORDER BY id_downloadInfo ASC) as newest
    FROM downloadinfo
)
SELECT * FROM added_row_number
WHERE newest = 1;

And it produces the exact result I'm looking for, which is the latest or newest time of download.

Any idea how can I merge those two queries to have it included within on query? Would be the best if the "DownloadDate" was another column next to "counter".

downloadinfo table has:

1. id_download info - unique and primary key
2. download date - current datetime passed from java script
3. files_id_files - id of file that is a foreign key from files table
4. users_id_users - id of user that is a foreign key from users table

files table has:

1. id_files - unique and primary key
2. users_id_users - foreign key for a user table
3. some other file infos

CodePudding user response：

have no idea your expected answer is this or not. what I feel is, you can use your WITH clause as a table like in below

left join (WITH added_row_number AS (
SELECT files_id_files, DownloadDate, ROW_NUMBER() OVER(PARTITION BY files_id_files 
ORDER BY id_downloadInfo ASC) as newest
FROM downloadinfo
) SELECT * FROM added_row_number
WHERE newest = 1) x on x.files_id_files  = files.files_id_files

and access your field next to your count(*) field like ,X.DownloadDate

Have no much idea about your primary keys available. So i used "x.files_id_files = files.files_id_files"

But why can't we use this as a subquery with limit command next to the Count(*)

Example,

   SELECT FileAlias, FileEXT, FileSize, FileExtension, UploadDate, category, COUNT(id_downloadInfo) as "counter", 
(SELECT DownloadDate FROM downloadinfo di
where di.files_id_files  = files.files_id_files 
order by DownloadDate desc limit 1) as DownloadDate 
    FROM files 
    JOIN categories ON files.category_id = categories.id_categories 
    JOIN users ON files.users_id_users = users.id_users 
    LEFT JOIN downloadinfo ON files.id_files = downloadinfo.files_id_files
    WHERE organisations_id_organisations = 1 AND UserOnly = 0
    GROUP BY files_id_files;

But if you need to access more columns than the date, WITH command is fine.