score:0
SELECT
ISB_No
,ISB_Ad
,ISDB_Sit
,ISBDB_Pers
,ISBDB_Date
FROM [TB-001_ISB] AS isb
INNER JOIN [TI-003_ISBDB] AS isdbd ON isb.ISB_No=isdbd.ISBDB_No
INNER JOIN [TB-002_ISDB] AS isdb ON isdb.ISDB_ID=isdbd.ISBDB_ISDB_ID
WHERE isdbd.ISBDB_Date = (select MAX(isdbd1.ISBDB_Date) FROM [TI-003_ISBDB] AS isdbd1
WHERE isdbd.ISBDB_No = isdbd1.ISBDB_No)
ORDER BY isdbd.ISBDB_Date DESC
score:1
Try something like this:
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY [ISBDB_No] ORDER BY [ISBDB_Date] DESC) Corr
FROM [TI-003_ISBDB]
)
SELECT B.*, [ISB_Ad], [ISDB_Sit]
FROM [TB-001_ISB] A
INNER JOIN (SELECT * FROM CTE WHERE corr = 1) B
ON A.[ISB_No] = B.[ISBDB_No]
INNER JOIN [TB-002_ISDB] C
ON C.[ISDB_ID] = B.[ISBDB_ISDB_ID]
More questions
- HAVING MAX in SQL Server 2005
- Is the max limit of 4 GB on sql server 2005 express .mdf + .ldf or for .mdf only?
- SQL Server 2005 Syntax Help - "Select Info based upon Max Value of Sub Query"
- Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
- How do you kill all current connections to a SQL Server 2005 database?
- SQL Server 2005 How Create a Unique Constraint?
- How to determine total number of open/active connections in ms sql server 2005
- How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?
- Get month and year from a datetime in SQL Server 2005
- How to export data from SQL Server 2005 to MySQL
- SQL Server 2005 implementation of MySQL REPLACE INTO?
- Diagnosing Deadlocks in SQL Server 2005
- Insert Picture into SQL Server 2005 Image Field using only SQL
- SQL Query to add a new column after an existing column in SQL Server 2005
- Size of VARBINARY field in SQL Server 2005
- SQL Server 2005 Using DateAdd to add a day to a date
- How to export SQL Server 2005 query to CSV
- SQL Server 2005 drop column with constraints
- Sql Query to list all views in an SQL Server 2005 database
- Sql Server 2005 how to change dbo login name
- SQL Server 2005 - Export table programmatically (run a .sql file to rebuild it)
- finding max possible date in ms sql server 2005+
- Atomic UPSERT in SQL Server 2005
- How do you transfer or export SQL Server 2005 data to Excel
- integer Max value constants in SQL Server T-SQL?
- SQL Server 2005 and temporary table scope
- Microsoft SQL Server 2005 Console Write Line
- Linking ALL Users to Login after restoring a SQL Server 2005 database
- SQL Server 2005 Setting a variable to the result of a select query
- Subtract minute from DateTime in SQL Server 2005
More questions with similar tag
- How to select all column in XML
- substring or rtrim
- Possible to INSERT with no rows in VALUES?
- add a bit of text from one column, into new column?
- Are statistics automatically updated when a new index is created?
- What is the use of OUTPUT clause in sql server
- SqlDataReader does not have the correct columns count
- Discrepancy in result when calculating time using decimal(10,1)
- Pass List as XElement to be used as XML Datatype parameter
- SQL Server 2016 non clustered index is not being used while ordering on clustering index
- Get TOP rows out of child table WITH inner join
- TSQL Including fiscal year in select statement based on the date
- SQL: How to have a non-unique "identity column", with automatic SET behaviour?
- ADO adTinyInt vs adUnsignedTinyInt with SQL Server
- ROW_Count in VIEW
- How to update a column from another column in the same table in a list of rows?
- use case in where clause sql server
- SELECTING from same field in 2 column
- Database SQL server migration from 2008 to 2014
- How to use a SQL output parameter with FromSqlInterpolated? Or alternative
- Including 0 in count does not work with extra where clause
- What syntax does the Current Language property take in a SQL Server connection string?
- Converting three int parameter to one date
- Can I query a database property without specifying the database name directly?
- Convert datetime to specific format?
- ASP.NET Core Web Api publishing to IIS producing an Sql Server connection error
- distinct date in where clause possible?
- How to run an Insert statement for each item in the SQL Table?
- SQL Recursive CTE Replacing records in each recursion
- Generic trigger on update / delete to backup current row
Source:
stackoverflow.com