SQL Server Max and Min records from 2 tables [closed]

gkn4icbw 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(118)

Closed. This question needs debugging details . It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem . This will help others answer the question.

Closed yesterday.
Improve this question

I have two tables. The first table has columns fileID , createdate . The second table has userid , fileID , createdate as common fields along with other columns.

I am trying to write a query to find latest fileid(max) and the first loaded fileid(min) based on the createdate for a specific userid by joining both these tables and using group by on fileid, createdate in the query and filtering the user id in the where clause.

However the result is showing all the rows.

I need a suggestion as how to write a query to get two records(max and min fileid records) only from both these tables and not all the records with these field changes.

I am using SQL Server to write the query.

sql-server

来源：https://stackoverflow.com/questions/74846280/max-and-min-records-from-2-tables

1条答案

按热度按时间

xkftehaa1#

To select fieldid by earliest or latest createdate and to have it in two separate rows you can try something like this:

SELECT fileid, "earliest" as type
FROM table1
WHERE createdate = (SELECT MIN(createdate) from table1)
LIMIT 1
UNION ALL
SELECT fileid, "lattest" as type
FROM table1
WHERE createdate = (SELECT MAX(createdate) from table1)
LIMIT 1

It is not clear, why you want to join it with the second table, but you can do it like this:

SELECT
 * 
FROM
  (
    SELECT fileid, "earliest" as type FROM table1 WHERE createdate = (SELECT 
    MIN(createdate) from table1) LIMIT 1
    UNION ALL
    SELECT fileid, "lattest" as type FROM table1 WHERE createdate = (SELECT 
    MAX(createdate) from table1) LIMIT 1
  ) as subquery1
LEFT JOIN
  table2 on table2.fileid = createdate.fileid

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Max and Min records from 2 tables [closed]

1条答案

相关问题

热门标签

最新问答